Return All Matches and Then Move to Next?

B

bkrupa

New Member
Joined
Jun 29, 2018
Messages
17
Hi,

I have been racking my brain with this for days now trying different things but keep ending up with circular references when I think I am close - I do not think there is anyway to do this without VBA if it is even possible with VBA as I haven't found anything close in searching.


In the first sheet I have a list of product family names that repeat up to 50 times in rows but each row has a unique code next to the family name in another column. This goes on for about 2000 rows.

IE:
Houses | 5656
Houses | 6581
Houses | 6568
Houses | 7878
Houses | 9654
Trucks |6655
Trucks | 5656
Trucks | 6363
Trucks | 2236

On another sheet I would like to be able to type a family name in column A row 1 and in column B row 1 have all up to 50 unique codes for that family name returned (there isn't always the same number of entries).

IE:
Houses |5656
...........| 6581
...........| 6568
...........| 7878

Then if I type another family name in column A row 2 have all of those unique codes that match to the family return under the last column B returned value.

IE:
Houses |5656
Trucks | 6581
..........| 6568
..........| 7878
..........| 6655
..........| 5656
..........| 6363
..........| 2236

I would need to enter up to about 30 max family names in column A to return up to 1500 values. The intention is that I would have 30 drop down boxes in column A and that would build my list of codes which would then lookup values for them out of a data pool.

Is this even possible without advance VBA?

Thank-you a ton for any advice!!

-Bkrup
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Something like this

ABCDEFG
HousesHouses
Houses
Houses
Houses
Houses
Trucks
Trucks
Trucks
Trucks

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]5656[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5656[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6581[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6568[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6568[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6581[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7878[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7878[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9654[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9654[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6655[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5656[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]6363[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2236[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G7[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G8[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G9[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Then just copy the formula down as far as you need, i.e. 50 rows.
 
Last edited:
Upvote 0
Thank-you a ton! This works great for the first entry but when I put a second entry in column F in the example above I would like it to continue building on the list of codes in column G.

IE:


ABCDEFG
HousesHouses
Houses
Houses
Houses
Houses
Trucks6655
Trucks5656
Trucks6363
Trucks2236

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]5656[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5656[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6581[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Trucks[/TD]
[TD="align: right"]6568[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6568[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6581[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7878[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7878[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9654[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9654[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6655[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5656[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]6363[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2236[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​



lrobbo314 said:
Something like this

ABCDEFG
HousesHouses
Houses
Houses
Houses
Houses
Trucks
Trucks
Trucks
Trucks

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]5656[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5656[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6581[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6568[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6568[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6581[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7878[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7878[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9654[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9654[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6655[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5656[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]6363[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2236[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G1[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH]G3[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH]G4[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH]G5[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH]G6[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH]G7[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH]G8[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
[TR]
[TH]G9[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF($A$1:$A$9=$F$1,$B$1:$B$9),ROW()),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Then just copy the formula down as far as you need, i.e. 50 rows.
Click to expand...
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
ABCDEFG
HousesHouses
Houses
Houses
Houses
Houses
Trucks
Trucks
Trucks
Trucks

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]5656[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5656[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6581[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6568[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6568[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6581[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7878[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7878[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9654[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9654[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6655[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5656[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]6363[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2236[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

</body>
 
Upvote 0
Put the following code in the events of your sheet2 (result sheet).
Change "sheet1" in the code to the name of the sheet that contains all the families.


You can put several families in column A, add one by one or put several, or delete some or all.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column > 1 Then Exit Sub
  If Target.Count > 50 Then Exit Sub
  Dim sh As Worksheet, c As Range, ary As Variant
  Application.ScreenUpdating = False
  Set sh = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
  ary = Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)).Value2)
  sh.Range("A1").AutoFilter 1, ary, xlFilterValues
  sh.Range("B:B").Copy Range("B1")
  sh.ShowAllData
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work (result sheet), select view code and paste the code into the window that opens up.
 
Upvote 0
What on earth......you just solved days of messing with this in 5 mins.

If I am ever in Mexico I owe you a case of beer.

I applied it to my test file and it worked great.....now going to try my working file. Fingers crossed!!


DanteAmor said:
Put the following code in the events of your sheet2 (result sheet).
Change "sheet1" in the code to the name of the sheet that contains all the families.


You can put several families in column A, add one by one or put several, or delete some or all.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column > 1 Then Exit Sub
  If Target.Count > 50 Then Exit Sub
  Dim sh As Worksheet, c As Range, ary As Variant
  Application.ScreenUpdating = False
  Set sh = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
  ary = Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)).Value2)
  sh.Range("A1").AutoFilter 1, ary, xlFilterValues
  sh.Range("B:B").Copy Range("B1")
  sh.ShowAllData
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work (result sheet), select view code and paste the code into the window that opens up.
Click to expand...
 
Upvote 0
Glad to know that it works for you. I gladly accept the beer! :beerchug:
 
Upvote 0
When I applied to to my main file I got a 1004 error and the debugger highlighted the last line of code "sh.ShowAllData". When I ended the debugger it had pulled almost all of the codes for all values. Thoughts on what I did wrong?
 
Upvote 0
bkrupa said:
When I applied to to my main file I got a 1004 error and the debugger highlighted the last line of code "sh.ShowAllData". When I ended the debugger it had pulled almost all of the codes for all values. Thoughts on what I did wrong?
Click to expand...

Is it because my workbook has multiple sheets?
 
Upvote 0
Try this please

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column > 1 Then Exit Sub
  If Target.Count > 100 Then Exit Sub
  Dim sh As Worksheet, c As Range, ary As Variant
  Application.ScreenUpdating = False
  Set sh = Sheets("Sheet1")
  Range("B:B").ClearContents
  If WorksheetFunction.CountA(Range("A1", Range("A" & Rows.Count).End(xlUp))) = 0 Then Exit Sub
  ary = Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)).Value2)
  sh.Range("A1").AutoFilter 1, ary, xlFilterValues
  sh.Range("B:B").Copy Range("B1")
  On Error Resume Next
  sh.ShowAllData
End Sub
 
Upvote 0
I think I figured it out. There is sometimes blanks in the source data. Is that what the above VBA is fixing? I am sorry for all of the questions I am only starting to learn to write VBA.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Return all non duplicates from a range
Replies
8
Views
195
Peter_SSs
Peter_SSs
R
  • Solved
MATCH Formula to Return multiple Row Numbers.
Replies
5
Views
266
Alex Blakenburg
A
J
  • Question Question
Split worksheets into separate excels AND only save those cells with data in them.
Replies
4
Views
302
mumps
mumps
K
  • Question Question
VLOOKUP on one set of criteria and return multiple matches in a row?
Replies
7
Views
376
Peter_SSs
Peter_SSs
J
  • Solved
Move Changed Rows to another sheet
2
Replies
15
Views
240
JGOpsMgr
J

Forum statistics

Threads
1,223,157
Messages
6,170,418
Members
452,325
Latest member
BlahQz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top