Return All Matches and Then Move to Next?

[bkrupa]

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

 

[lrobbo314]

Something like this

	A	B	C	D	E	F	G
1	Houses	5656				Houses	5656
2	Houses	6581					6568
3	Houses	6568					6581
4	Houses	7878					7878
5	Houses	9654					9654
6	Trucks	6655
7	Trucks	5656
8	Trucks	6363
9	Trucks	2236

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

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

<tbody>
</tbody>

Then just copy the formula down as far as you need, i.e. 50 rows.

 

[bkrupa]

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:

	A	B	C	D	E	F	G
1	Houses	5656				Houses	5656
2	Houses	6581				Trucks	6568
3	Houses	6568					6581
4	Houses	7878					7878
5	Houses	9654					9654
6	Trucks	6655					6655
7	Trucks	5656					5656
8	Trucks	6363					6363
9	Trucks	2236					2236

<tbody>
</tbody>

Sheet2​

Something like this

	A	B	C	D	E	F	G
1	Houses	5656				Houses	5656
2	Houses	6581					6568
3	Houses	6568					6581
4	Houses	7878					7878
5	Houses	9654					9654
6	Trucks	6655
7	Trucks	5656
8	Trucks	6363
9	Trucks	2236

<tbody>
</tbody>

Sheet2​

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

<tbody>
</tbody>

Then just copy the formula down as far as you need, i.e. 50 rows.

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

	A	B	C	D	E	F	G
1	Houses	5656				Houses	5656
2	Houses	6581					6568
3	Houses	6568					6581
4	Houses	7878					7878
5	Houses	9654					9654
6	Trucks	6655
7	Trucks	5656
8	Trucks	6363
9	Trucks	2236

<tbody>
</tbody>

Sheet2​

</body>

 

[DanteAmor]

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.

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.

 

[bkrupa]

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!!

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.

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.

 

[DanteAmor]

Glad to know that it works for you. I gladly accept the beer!

 

[bkrupa]

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?

 

[bkrupa]

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?

Is it because my workbook has multiple sheets?

 

[DanteAmor]

Try this please

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

 

[bkrupa]

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.