Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)

Some additional notes and extended explanations in support of answer to this Thread
https://eileenslounge.com/viewtopic....fc302dc186f01b

The OP has this
_____ Workbook: SampleSept2020.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G
1	Header1	Header2	Header3	Header4	Header5	Header6	Header7
2	101	H2_1	H3_1	H4_1	H5_1	H6_1	H7_1
3	102	H2_2	H3_2	H4_2	H5_2	H6_2	H7_2
4	103	H2_3	H3_3	H4_3	H5_3	H6_3	H7_3
5	102	H2_4	H3_4	H4_4	H5_4	H6_4	H7_4
6	101	H2_5	H3_5	H4_5	H5_5	H6_5	H7_5
7	103	H2_6	H3_6	H4_6	H5_6	H6_6	H7_6
8	105	H2_7	H3_7	H4_7	H5_7	H6_7	H7_7
9	104	H2_8	H3_8	H4_8	H5_8	H6_8	H7_8

Worksheet: Source

This what the OP wants
_____ Workbook: SampleSept2020.xlsm ( Using Excel 2007 32 bit )

Row\Col	C	D	E	F
2	MyTarget	Header3	Header4	Header7
3	101	H3_1	H4_1	H7_1
4	101	H3_5	H4_5	H7_5
5	101
6	102	H3_2	H4_2	H7_2
7	103	H3_3	H4_3	H7_3
8	103	H3_6	H4_6	H7_6
9	104	H3_8	H4_8	H7_8
10	108
11	105	H3_7	H4_7	H7_7

Worksheet: Target

Here again what the OP wants, with explanations:

	Expected Result
MyTarget	Header3	Header4	Header7
101	H3_1	H4_1	H7_1		first instance
101	H3_5	H4_5	H7_5		second instance
101					third instance (there is no third instance so left empty)
102	H3_2	H4_2	H7_2
103	H3_3	H4_3	H7_3
103	H3_6	H4_6	H7_6
104	H3_8	H4_8	H7_8
108					left empty as there is no 108 in Source
105	H3_7	H4_7	H7_7

You can see that it comes from the source worksheet:

Header1	Header2	Header3	Header4	Header5	Header6	Header7
101	H2_1	H3_1	H4_1	H5_1	H6_1	H7_1
102	H2_2	H3_2	H4_2	H5_2	H6_2	H7_2
103	H2_3	H3_3	H4_3	H5_3	H6_3	H7_3
102	H2_4	H3_4	H4_4	H5_4	H6_4	H7_4
101	H2_5	H3_5	H4_5	H5_5	H6_5	H7_5
103	H2_6	H3_6	H4_6	H5_6	H6_6	H7_6
105	H2_7	H3_7	H4_7	H5_7	H6_7	H7_7
104	H2_8	H3_8	H4_8	H5_8	H6_8	H7_8

---

Rem 1
The main start point on my logic is obtaining ( in a dynamic way ) a range ( in an array , arrSrch() , ) that looks like this

Header1	Header3	Header4	Header7
101	H3_1	H4_1	H7_1
102	H3_2	H4_2	H7_2
103	H3_3	H4_3	H7_3
102	H3_4	H4_4	H7_4
101	H3_5	H4_5	H7_5
103	H3_6	H4_6	H7_6
105	H3_7	H4_7	H7_7
104	H3_8	H4_8	H7_8

Note: The array arrSrch() has an extra empty row
https://imgur.com/fKjli8W https://i.imgur.com/fKjli8W.jpg


What we do with that is the subject of Rem 2 , and is explained in the next post

Continued from last post

Rem 2
We build up a Main 1D array whose elements are themselves 1 D arrays of the required output rows.
This is done by looping down the target range rows , arTgt() = WsT.Range("C2:C" & LrT & "").Value
At each loop we look for a match of the target range row value in the first column of arrSrch()
We then do the array Split type technique ( https://excelfox.com/forum/showthrea...lication-Index ) to get a 1 D array of the required row. That row is added to the Main 1 D array
We then remove that row from arrSrch() ( using a function from Rick Rothstein https://excelfox.com/forum/showthrea...-Variant-Array ).
Then we move on to the next target range row down

Rem 3
Our output array is a 1D array of 1D arrays , but we noticed that we can treat that in Index as a 2D array https://eileenslounge.com/viewtopic....266691#p266691
For demo purposes, the macro in the next post pastes out the result in a spare worksheet range:

Code:

---

' Example paste out  CHANGE  Top left cell  H35  to suit
 Let WsT.Range("H35").Resize(UBound(arrOut(), 1) - 1, UBound(arrOut(), 2)).Value = arrOut()                ' ** -1 is a bodge to knock off the extra row
End Sub


---

_____ Workbook: SampleSept2020.xlsm ( Using Excel 2007 32 bit )

Row\Col	H	I	J
35	H3_1	H4_1	H7_1
36	H3_5	H4_5	H7_5
37
38	H3_2	H4_2	H7_2
39	H3_3	H4_3	H7_3
40	H3_6	H4_6	H7_6
41	H3_8	H4_8	H7_8
42
43	H3_7	H4_7	H7_7

Worksheet: Target

Macro, Sub BrdShlss() , and a couple of required Functions are here:
https://excelfox.com/forum/showthrea...ll=1#post14907

Macro for these posts
https://excelfox.com/forum/showthrea...ll=1#post14905
https://excelfox.com/forum/showthrea...ll=1#post14906
http://www.eileenslounge.com/viewtopic.php?f=30&t=35303

Code:

---

Option Explicit
Sub BrdShlss() '  http://www.eileenslounge.com/viewtopic.php?f=30&t=35303  https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=14907&viewfull=1#post14907
Rem 1 worksheets data info
Dim WsS As Worksheet, WsT As Worksheet
 Set WsS = ThisWorkbook.Worksheets("Source"): Set WsT = ThisWorkbook.Worksheets("Target")
Dim LrS As Long, LrT As Long, LcS As Long, LcT As Long
 Let LrS = WsS.Range("A" & WsS.Rows.Count & "").End(xlUp).Row
 Let LrT = WsT.Range("C" & WsT.Rows.Count & "").End(xlUp).Row
 Let LcS = WsS.Cells(1, WsS.Columns.Count).End(xlToLeft).Column
 Let LcT = WsT.Cells(2, WsT.Columns.Count).End(xlToLeft).Column
Dim arSrc() As Variant ', arSrcA() As Variant
 Let arSrc() = WsS.Range("A1:" & CLtr(LcS) & LrS + 1 & "").Value  '  + 1 is to give us an extra empty row
' Let arSrcA() = WsS.Range("A1:A" & LrS & "").Value
Dim arTgt() As Variant: Let arTgt() = WsT.Range("C2:C" & LrT & "").Value
'1b) determine what columns are needed for our search range, since typically not all are needed
Dim strClms As String: Let strClms = "1"
Dim SrchHd() As Variant: Let SrchHd() = WsT.Range("D2:" & CLtr(LcT) & "2").Value
Dim SrcHd() As Variant: Let SrcHd() = WsS.Range("A1:" & CLtr(LcS) & "1").Value
Dim Cnt As Long
    For Cnt = 1 To UBound(SrchHd(), 2)
    Dim MtchRes As Long ' Note I assume there is always a match in Headers between sheet ranges, so that I always have a number and not an error string
    Let MtchRes = Application.Match(SrchHd(1, Cnt), SrcHd(), 0)
    Let strClms = strClms & " " & MtchRes  ' add a required column indicie
   
    Next Cnt
' Let strClms = Left(strClms, (Len(strClms) - 1)) ' remove last unwanted space  For the given example this gives us  "3 4 7"
Dim RwsT() As Variant: Let RwsT() = Evaluate("=Row(1:" & LrS + 1 & ")") '  + 1 is to give us an extra empty row
Dim arrSrch() As Variant ' This will be the reduced size range we need to search in - it has just the headers required
 Let arrSrch() = Application.Index(arSrc(), RwsT(), Split(strClms, " ", -1, vbBinaryCompare)) ' In our example  Split(strClms, " ", -1, vbBinaryCompare))  is  {1, 3, 4, 7)
' Let Range("H24").Resize(UBound(arrSrch(), 1), UBound(arrSrch(), 2)).Value = arrSrch()
'1c) Get initial row string indicies for current source range
'Dim RwsT() As Variant: Let RwsT() = Evaluate("=Row(1:" & UBound(arSrc(), 1) & ")") ' Typical "vertical" array of row indices needed in  Index(Arr, Rws(), Clms())  type code line
'Dim Rws() As Variant: Let Rws() = Application.Index(RwsT(), Evaluate("=Column(A:" & CLtr(UBound(RwsT, 1)) & ")"), Evaluate("=Column(A:" & CLtr(UBound(RwsT(), 1)) & ")/Column(A:" & CLtr(UBound(RwsT(), 1)) & ")")) '  Transpose the  "vertical array to get a 1 Dimenrional "horizontal" array
'Dim strRws As String: Let strRws = " " & Join(Rws(), " ") & " " ' This is a string of our row indicies, and later we will remove some indicies as we go along then work the steps above backwards to get a modified  RwsT()  to use in  Index(Arr, Rws(), Clms())  type code line  for a new reduced content search array
Rem 2 Building output array
Dim arrOut() As Variant ' A 1 D array for the 1 D arrays at each match
' 2b) main loop for all rows of  MyTarget
    For Cnt = 2 To UBound(arTgt(), 1) Step 1
    ReDim Preserve arrOut(1 To Cnt - 1)
    Dim arSrcA() As Variant: Let arSrcA() = Application.Index(arrSrch(), 0, 1) ' the first column of our current  arrSrch()    '  https://excelfox.com/forum/showthread.php/1111-VBA-Trick-of-the-Week-Slicing-an-Array-Without-Loop-%e2%80%93-Application-Index
    Dim VarMtchres As Variant
    Let VarMtchres = Application.Match(arTgt(Cnt, 1), arSrcA(), 0)
        If IsError(VarMtchres) Then ' we need to add an empty row which we have as the last row of  arrSrch()
        Let arrOut(Cnt - 1) = Application.Index(arrSrch(), UBound(arrSrch(), 1), 0)                                          '  https://excelfox.com/forum/showthread.php/1111-VBA-Trick-of-the-Week-Slicing-an-Array-Without-Loop-%e2%80%93-Application-Index
        Else
        Let arrOut(Cnt - 1) = Application.Index(arrSrch(), VarMtchres, 0)                                                    '  https://excelfox.com/forum/showthread.php/1111-VBA-Trick-of-the-Week-Slicing-an-Array-Without-Loop-%e2%80%93-Application-Index
    '2b(ii) we must remove the row from the arrSrch()
        Let arrSrch() = DeleteArrayRow(arrSrch(), (VarMtchres))
        End If
    Next Cnt
Rem 3  '  Our output array is a 1D array of 1D arrays , but we noticed that we can treat that in  Index  as a 2D array  https://eileenslounge.com/viewtopic.php?p=266691#p266691
 Let arrOut() = Application.Index(arrOut(), RwsT(), Evaluate("=Column(B:" & CLtr(UBound(arrSrch(), 2)) & ")"))  ' ** this is actually 1 row too big
' Example paste out  CHANGE  Top left cell  H35  to suit
 Let WsT.Range("H35").Resize(UBound(arrOut(), 1) - 1, UBound(arrOut(), 2)).Value = arrOut()                ' ** -1 is a bodge to knock off the extra row
End Sub

'  https://excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array
Function DeleteArrayRow(Arr As Variant, RowToDelete As Long) As Variant
  Dim Rws As Long, Cols As String
  Rws = UBound(Arr) - LBound(Arr)
  Cols = "A:" & Split(Columns(UBound(Arr, 2) - LBound(Arr, 2) + 1).Address(, 0), ":")(0)
  DeleteArrayRow = Application.Index(Arr, Application.Transpose(Split(Join(Application.Transpose(Evaluate("Row(1:" & (RowToDelete - 1) & ")"))) & " " & Join(Application.Transpose(Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(Arr) & ")"))))), Evaluate("COLUMN(" & Cols & ")"))
End Function

'  https://excelfox.com/forum/showthread.php/1902-Function-Code-for-getting-Column-Letter-from-Column-Number
Public Function CLtr(ByVal lclm As Long) As String '        http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CLtr = Chr(65 + (((lclm - 1) Mod 26))) & CLtr: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

---

Some notes in support of these Threads
https://excelfox.com/forum/showthrea...urning-Nothing
https://excelfox.com/forum/showthrea...sult-Using-VBA


Google Browser Page HTML Source
Typically,
_ the first main section in internet page manipulation codings which try to get things from internet sites, is a code section which gets you a single, very long, text string of something similar to what your browser actually uses to present all you see.
( Google Browser also allows you to see in the browser all that text if you right click and select something like Show Page Source ( or use short cut key combination of Strg+u )
ShowPageSource.JPG PageSource.JPG :
https://imgur.com/UnAs5Le , https://imgur.com/bubFTet
https://i.imgur.com/UnAs5Le.jpg , https://i.imgur.com/bubFTet.jpg )


I am not 100% familiar with all the syntaxes and workings of this first code section, but usually they are similar in such codings, and usually I can get that code section to get the HTML page Source text string, ( and we can add a few extra code lines if we want to put all that text string into a text file , so that we can look at it , and use the simple search facility within a text editor, such as Notepad , to find things in that very long text string )
This first code section will get me that text string for a Google Search of ExcelFox , and it will put it in a text file with the name
GoogleSrchExcelFox.txt

Code:

---

Sub GoogleSearchURL()  '    https://excelfox.com/forum/showthread.php/2656-Automated-Search-Results-Returning-Nothing            https://excelfox.com/forum/showthread.php/973-Lookup-First-URL-From-Google-Search-Result-Using-VBA
 On Error GoTo Bed
'_1 First section get the long text string of the HTML coding of the internet Page
'_1(i) get the long single text string
    With CreateObject("msxml2.xmlhttp")
    .Open "GET", "https://www.google.com/search?q=ExcelFox", False ' 'just preparing the request type, how and what type... "The True/False argument of the HTTP Request is the Asynchronous mode flag. If set False then control is immediately returns to VBA after Send is executed. If set True then control is returned to VBA after the server has sent back a response.
    'No extra info here for type GET
    .setRequestHeader bstrheader:="Ploppy", bstrvalue:="Poo"
    '.setRequestHeader bstrheader:="If-Modified-Since", bstrvalue:="Sat, 1 Jan 2000 00:00:00 GMT" '  https://www.autohotkey.com/boards/viewtopic.php?t=9554  ---  It will caching the contents of the URL page. Which means if you request the same URL more than once, you always get the same responseText even the website changes text every time. This line is a workaround : Set cache related headers.
    .send ' varBody:= ' No extra info for type GET. .send actually makes the request
        While .readyState <> 4: DoEvents: Wend ' Allow other processes to run while the web page loads. Think this is part of the True option
    Dim PageSrc As String: Let PageSrc = .responseText ' Save the HTML code in the (Global) variable. ': Range("P1").Value = PageSrc 'For me for a print out copy to text file etc.    The responseText property returns the information requested by the Open method as a text string
    End With
'_1(ii)  Optional secion  to put the text string into a text file , for ease of code developments
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName2 As String
 Let PathAndFileName2 = ThisWorkbook.Path & "\" & "GoogleSrchExcelFox" & ".txt"  ' CHANGE TO SUIT
 Open PathAndFileName2 For Output As #FileNum2 ' CHANGE TO SUIT  ' Will be made if not there
 Print #FileNum2, PageSrc '
 Close #FileNum2


---

_ The second part of such internet page manipulation coding involve often putting that text into an Object that allows a Object oriented programming type analysis of the web page. That is rather advanced and I personally am not too experienced with that.

What I am proposing is a much simplified approach I used myself successfully a few times. It is so simple, that I guess it may not be reliable permanently, for example, when a small change is made to the source page coding by Google. On the other hand , often major changes make the more advanced coding no longer work.
My solution is probably best only to use if you can understand enough to modify it yourself later when it no longer works. That is why I will explain it in detail here.

Examine the string to find the info you want
My solution does very simple basic string manipulation to pick out what I want.
As example, I do google search for ExcelFox manually and programmatically…_
_ Manually:
https://imgur.com/M16cko3 : https://i.imgur.com/M16cko3.jpg
_ Programmatically :
I run the macro snippet above, and look at the text file produced in a text editor. Then I use the search option to look for ExcelFox
NotepadSearch.JPG , Notepad Search.JPG
https://imgur.com/L9dcXBf , https://imgur.com/K4kl3qk
https://i.imgur.com/L9dcXBf.jpg , https://i.imgur.com/K4kl3qk.jpg
If I compare the results of manually and programmatically, then I can pick out a pattern. ( Note: you must look at all the occurrences of ExcelFox – Some will be as part of a text that you don’t want, but you will see a match between the things shown manually, and the text got programmatically.
Example
My manual search got me this: ExcelFoxManaulGooglesearch.JPG : https://imgur.com/M16cko3
https://i.imgur.com/M16cko3.jpg
Consider the first three main URLs given by the search :
http://www.excelfox.com/forum/forum.php
https://excelfox.com/forum/forumdisp...p/2-Excel-Help
http://www.hifi-forum.de/bild/excel-...0e_737672.html
If I search in the text file, I can pick out those inside a similar text section…

Code:

---

'                                                                                                  q=ExcelFox&amp;source=lnms&amp;tbm=nws&amp;sa=X&amp;ved=0ahUKEwjO9PiFs6jsAhXJzoUKHUj-DwcQ_AUIBygD">NEWS</a></td></tr></tbody></table></div></div><div><div> <div> <div class="ezO2md"><div><div><a class="fuLhoc ZWRArf" href="/url?q=http://www.excelfox.com/forum/forum.php&amp;sa=U&amp;ved=2ahUKEwjO9PiFs6jsAhXJzoUKHUj-DwcQFjAAegQIBxAB&amp;usg=AOvVaw3c8Z4i7W8Ooq7f9a8C3CKw"><span class="CVA68e qXLe6d">Excel,
'  <span class="qXLe6d FrIlee">  <span class="fYyStc">Have a question in Excel, Access, Powerpoint, Word or Outlook? Ask http://www.?excelfox.com/forum/forum.php.</span>  </span>      </div>  </div></td></tr></table></div></div></div> </div> </div><div> <div> <div class="ezO2md"><div><div><a class="fuLhoc ZWRArf" href="/url?q=https://excelfox.com/forum/forumdisplay.php/2-Excel-Help&amp;sa=U&amp;ved=2ahUKEwjO9PiFs6jsAhXJzoUKHUj-
'                                                                                                                              Weitere Ergebnisse von excelfox.com</a>  </span>          </div>  </div></td></tr></table></div></div></div> </div> </div><div> <div> <div class="ezO2md"><div><div><a class="fuLhoc ZWRArf" href="/url?q=http://www.hifi-forum.de/bild/excel-fox-700e_737672.html&amp;sa=U&amp;ved=2ahUKEwjO9PiFs6jsAhXJzoUKHUj-DwcQFjACegQIABAB&amp;usg=AOvVaw1WljIWpaSLwuTcgdbTcLeU"><span class="CV

---

I now repeat the above experiment for a Google search on Chandoo
Manual search results:
ChandooManaulGooglesearch.JPG : https://imgur.com/eQSDHsz
https://i.imgur.com/eQSDHsz.jpg
Considering again just the first 3 results , we have
https://chandoo.org/
https://www.youtube.com/channel/UC8u...MHeeRma49dtZKA
https://de.wikipedia.org/wiki/Chandu
Programmatic ( looking through the produced text file to find something similar to the first 3 URLs from the manual search)
( This would be the macro to get the text file from that search : https://excelfox.com/forum/showthrea...ll=1#post14992 )

Code:

---

'            /table></div></div></div> </div> </div><div> <div> <div class="ezO2md"><div><div><a class="fuLhoc ZWRArf" href="/url?q=https://chandoo.org/&amp;sa=U&amp;ved=2ahUKEwiFs9-r4KrsAhWNC-wKHSLMBb0QFjACegQICBAB&
'                                                                <div class="ezO2md"><div><div><a class="fuLhoc ZWRArf" href="/url?q=https://www.youtube.com/channel/UC8uU_wruBMHeeRma49dtZKA&amp;sa=U&amp;ved=2ahUKEwiFs9-r4KrsA
'  /td></tr></table></div></div></div> </div> </div><div> <div> <div class="ezO2md"><div><div><a class="fuLhoc ZWRArf" href="/url?q=https://de.wikipedia.org/wiki/Chandu&amp;sa=U&amp;ved=2ahUKEwiFs9-r4KrsAhWNC-wKHSLMBb0QFjAEegQIARAB&amp;usg=AOvVaw323MmSfVaurlycQW8E02XJ"><span class="CVA68e qXLe6d">Chandu – Wikipedia</span>  <span class="qXLe6d dX

---

Solution based on simple string analysis
It appears as if we can easily pick out our required URLs from the text if we look for some of the text appearing just before all the URLs.
We could try for example, class="fuLhoc ZWRArf" href="/url?q=
We know then that the text after is out wanted URL
We can also see that we have consistently the same string after URL, so we know we can look for that in order to know the end of the URL text
The implementation of this is fairly simple VBA string manipulation.

Some notes in support of these Threads
https://excelfox.com/forum/showthrea...urning-Nothing
https://excelfox.com/forum/showthrea...sult-Using-VBA

Code:

---

Sub GoogleSearchURL()  '    https://excelfox.com/forum/showthread.php/2656-Automated-Search-Results-Returning-Nothing            https://excelfox.com/forum/showthread.php/973-Lookup-First-URL-From-Google-Search-Result-Using-VBA
 On Error GoTo Bed
'_1 First section get the long text string of the HTML coding of the internet Page
'_1(i) get the long single text string
    With CreateObject("msxml2.xmlhttp")
    .Open "GET", "https://www.google.com/search?q=Chandoo", False ' 'just preparing the request type, how and what type... "The True/False argument of the HTTP Request is the Asynchronous mode flag. If set False then control is immediately returns to VBA after Send is executed. If set True then control is returned to VBA after the server has sent back a response.
    'No extra info here for type GET
    .setRequestHeader bstrheader:="Ploppy", bstrvalue:="Poo"
    '.setRequestHeader bstrheader:="If-Modified-Since", bstrvalue:="Sat, 1 Jan 2000 00:00:00 GMT" '  https://www.autohotkey.com/boards/viewtopic.php?t=9554  ---  It will caching the contents of the URL page. Which means if you request the same URL more than once, you always get the same responseText even the website changes text every time. This line is a workaround : Set cache related headers.
    .send ' varBody:= ' No extra info for type GET. .send actually makes the request
        While .readyState <> 4: DoEvents: Wend ' Allow other processes to run while the web page loads. Think this is part of the True option
    Dim PageSrc As String: Let PageSrc = .responseText ' Save the HTML code in the (Global) variable. ': Range("P1").Value = PageSrc 'For me for a print out copy to text file etc.    The responseText property returns the information requested by the Open method as a text string
    End With
'_1(ii)  Optional secion  to put the text string into a text file , for ease of code developments
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName2 As String
 Let PathAndFileName2 = ThisWorkbook.Path & "\" & "Chandoo" & ".txt"  ' CHANGE TO SUIT
 Open PathAndFileName2 For Output As #FileNum2 ' CHANGE TO SUIT  ' Will be made if not there
 Print #FileNum2, PageSrc '
 Close #FileNum2
End Sub


---

In support of this Thread
https://excelfox.com/forum/showthrea...tiple-Criteria
and answer
https://excelfox.com/forum/showthrea...ll=1#post15046

This is what the transpose of SM_T_D1() looks like ( SM_T_D1() is actually pseudo horizontal rather than vertical , as it is a 1D array )
_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Sales Man Territory Dimension

John New York Tissue

Alfred Washington Soda

John New York Soda

Alfred New York Tissue

Leo Washington Soda

Leo New York Tissue

Maxwell Washington Towel

Here is the equivalent transpose of array, SM_T_D2()
_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Sales Man Territory Dimension

John New York Tissue

John New York Soda

John New York Paper

John New York Towel

John Washington Tissue

John Washington Soda

John Washington Paper

John Washington Towel

Alfred New York Tissue

Alfred New York Soda

Alfred New York Paper

Alfred New York Towel

Alfred Washington Tissue

Alfred Washington Soda

Alfred Washington Paper

Alfred Washington Towel

Leo New York Tissue

Leo New York Soda

Leo New York Paper

Leo New York Towel

Leo Washington Tissue

Leo Washington Soda

Leo Washington Paper

Leo Washington Towel

Maxwell New York Tissue

Maxwell New York Soda

Maxwell New York Paper

Maxwell New York Towel

Maxwell Washington Tissue

Maxwell Washington Soda

Maxwell Washington Paper

Maxwell Washington Towel

In support of this answer
https://excelfox.com/forum/showthrea...ll=1#post15046


Before:
___ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E
1	Sales Man	Territory	Dimension	Sales Amt	Cost
2	John	New York	Tissue	1,000.00	200.00
3	Alfred	Washington	Soda	2,100.00	700.00
4	John	New York	Soda	2,050.00	1,500.00
5	Alfred	New York	Tissue	2,000.00	500.00
6	Leo	Washington	Soda	200.00	100.00
7	Leo	New York	Tissue	3,500.00	1,500.00
8	Maxwell	Washington	Towel	1,000.00	800.00

Worksheet: Export1


_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E
1	Sales Man	Territory	Dimension	Sales Amt	Cost
2	John	New York	Tissue
3	John	New York	Soda
4	John	New York	Paper
5	John	New York	Towel
6	John	Washington	Tissue
7	John	Washington	Soda
8	John	Washington	Paper
9	John	Washington	Towel
10	Alfred	New York	Tissue
11	Alfred	New York	Soda
12	Alfred	New York	Paper
13	Alfred	New York	Towel
14	Alfred	Washington	Tissue
15	Alfred	Washington	Soda
16	Alfred	Washington	Paper
17	Alfred	Washington	Towel
18	Leo	New York	Tissue
19	Leo	New York	Soda
20	Leo	New York	Paper
21	Leo	New York	Towel
22	Leo	Washington	Tissue
23	Leo	Washington	Soda
24	Leo	Washington	Paper
25	Leo	Washington	Towel
26	Maxwell	New York	Tissue
27	Maxwell	New York	Soda
28	Maxwell	New York	Paper
29	Maxwell	New York	Towel
30	Maxwell	Washington	Tissue
31	Maxwell	Washington	Soda
32	Maxwell	Washington	Paper
33	Maxwell	Washington	Towel

Worksheet: ResultVBA

In support of this Thread
https://excelfox.com/forum/showthrea...tiple-Criteria
and answer
https://excelfox.com/forum/showthrea...ll=1#post15046


After running Sub Arrays1()

_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E
1	Sales Man	Territory	Dimension	Sales Amt	Cost
2	John	New York	Tissue	1000	200
3	John	New York	Soda	2050	1500
4	John	New York	Paper
5	John	New York	Towel
6	John	Washington	Tissue
7	John	Washington	Soda
8	John	Washington	Paper
9	John	Washington	Towel
10	Alfred	New York	Tissue	2000	500
11	Alfred	New York	Soda
12	Alfred	New York	Paper
13	Alfred	New York	Towel
14	Alfred	Washington	Tissue
15	Alfred	Washington	Soda	2100	700
16	Alfred	Washington	Paper
17	Alfred	Washington	Towel
18	Leo	New York	Tissue	3500	1500
19	Leo	New York	Soda
20	Leo	New York	Paper
21	Leo	New York	Towel
22	Leo	Washington	Tissue
23	Leo	Washington	Soda	200	100
24	Leo	Washington	Paper
25	Leo	Washington	Towel
26	Maxwell	New York	Tissue
27	Maxwell	New York	Soda
28	Maxwell	New York	Paper
29	Maxwell	New York	Towel
30	Maxwell	Washington	Tissue
31	Maxwell	Washington	Soda
32	Maxwell	Washington	Paper
33	Maxwell	Washington	Towel	1000	800

Worksheet: ResultVBA

Some extra clarifying info for this thread
https://excelfox.com/forum/showthrea...tiple-Criteria
and specifically this post
https://excelfox.com/forum/showthrea...5048#post15048

For this range with Helper column

_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F
1	Sales Man	Territory	Dimension	Helper Column	Sales Amt	Cost
2	John	New York	Tissue	John|New York|Tissue	1,000.00	200.00
3	Alfred	Washington	Soda	Alfred|Washington|Soda	2,100.00	700.00
4	John	New York	Soda	John|New York|Soda	2,050.00	1,500.00
5	Alfred	New York	Tissue	Alfred|New York|Tissue	2,000.00	500.00
6	Leo	Washington	Soda	Leo|Washington|Soda	200.00	100.00
7	Leo	New York	Tissue	Leo|New York|Tissue	3,500.00	1,500.00
8	Maxwell	Washington	Towel	Maxwell|Washington|Towel	1,000.00	800.00

Worksheet: Export

_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Row\Col	D
1	Helper Column
2	=A2&"|"&B2&"|"&C2
3	=A3&"|"&B3&"|"&C3
4	=A4&"|"&B4&"|"&C4
5	=A5&"|"&B5&"|"&C5
6	=A6&"|"&B6&"|"&C6
7	=A7&"|"&B7&"|"&C7
8	=A8&"|"&B8&"|"&C8

Worksheet: Export



Formula VLookUp

_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE))	=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE))

Worksheet: ResultVLookUp


Formula Index

_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IF(ISERROR(INDEX(Export!$E$2:$E$8,MATCH(1,(ResultIndex!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1)),"",INDEX(Export!$E$2:$E$8,MATCH(1,(ResultIndex!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1))	=IF(ISERROR(INDEX(Export!$F$2:$F$8,MATCH(1,(ResultIndex!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1)),"",INDEX(Export!$F$2:$F$8,MATCH(1,(ResultIndex!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1))

Worksheet: ResultIndex

_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IFERROR(INDEX(Export!$E$2:$E$8,MATCH(1,(ResultIndex2!A2=Export!$A$2:$A$8)*(ResultIndex2!B2=Export!$B$2:$B$8)*(ResultIndex2!C2=Export!$C$2:$C$8),0),1),"")	=IFERROR(INDEX(Export!$F$2:$F$8,MATCH(1,(ResultIndex2!A2=Export!$A$2:$A$8)*(ResultIndex2!B2=Export!$B$2:$B$8)*(ResultIndex2!C2=Export!$C$2:$C$8),0),1),"")

Worksheet: ResultIndex2

Some extra clarifying info for this thread
https://excelfox.com/forum/showthrea...tiple-Criteria
and specifically this post
https://excelfox.com/forum/showthrea...5048#post15048

For this range

_____ Workbook: AllFormulasAndVBAMultipleCriteria.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E
1	Sales Man	Territory	Dimension	Sales Amt	Cost
2	John	New York	Tissue	1,000.00	200.00
3	Alfred	Washington	Soda	2,100.00	700.00
4	John	New York	Soda	2,050.00	1,500.00
5	Alfred	New York	Tissue	2,000.00	500.00
6	Leo	Washington	Soda	200.00	100.00
7	Leo	New York	Tissue	3,500.00	1,500.00
8	Maxwell	Washington	Towel	1,000.00	800.00

Worksheet: Export1


Index Formulas

From P45cal

_____ Workbook: AllFormulasAndVBAMultipleCriteria2.xlsm ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IF(ISERROR(INDEX(Export1!D$1:D$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0))),"",INDEX(Export1!D$1:D$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0)))	=IF(ISERROR(INDEX(Export1!E$1:E$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0))),"",INDEX(Export1!E$1:E$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0)))

Worksheet: P45cal

_____ Workbook: AllFormulasAndVBAMultipleCriteria2.xlsm ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IFERROR(INDEX(Export1!D$1:D$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0)),"")	=IFERROR(INDEX(Export1!E$1:E$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0)),"")

Worksheet: P45cal1



From Alan ( DocAElstein )

_____ Workbook: AllFormulasAndVBAMultipleCriteria2.xlsm ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IF(ISERROR(INDEX(Export1!$D$2:$D$8,MATCH(1,(ResIndex!A2=Export1!$A$2:$A$8)*(ResIndex!B2=Export1!$B$2:$B$8)*(ResIndex!C2=Export1!$C$2:$C$8),0),1)),"",INDEX(Export1!$D$2:$D$8,MATCH(1,(ResIndex!A2=Export1!$A$2:$A$8)*(ResIndex!B2=Export1!$B$2:$B$8)*(ResIndex!C2=Export1!$C$2:$C$8),0),1))	=IF(ISERROR(INDEX(Export1!$E$2:$E$8,MATCH(1,(ResIndex!A2=Export1!$A$2:$A$8)*(ResIndex!B2=Export1!$B$2:$B$8)*(ResIndex!C2=Export1!$C$2:$C$8),0),1)),"",INDEX(Export1!$E$2:$E$8,MATCH(1,(ResIndex!A2=Export1!$A$2:$A$8)*(ResIndex!B2=Export1!$B$2:$B$8)*(ResIndex!C2=Export1!$C$2:$C$8),0),1))

Worksheet: ResIndex

_____ Workbook: AllFormulasAndVBAMultipleCriteria2.xlsm ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IFERROR(INDEX(Export1!$D$2:$D$8,MATCH(1,(ResIndex2!A2=Export1!$A$2:$A$8)*(ResIndex2!B2=Export1!$B$2:$B$8)*(ResIndex2!C2=Export1!$C$2:$C$8),0),1),"")	=IFERROR(INDEX(Export1!$E$2:$E$8,MATCH(1,(ResIndex2!A2=Export1!$A$2:$A$8)*(ResIndex2!B2=Export1!$B$2:$B$8)*(ResIndex2!C2=Export1!$C$2:$C$8),0),1),"")

Worksheet: ResIndex2