More Than - Vlookup

[MGumbrell]

Hi all

On 'Sheet1' Range A1 the user enters a "product code" and using Vlookup on a range on 'Sheet2' a number of ranges on 'sheet1' are populated.

The issue is that using Vlookup I can only populate with the first entry that meets the criteria. The problem is that the "product code" entered by the user may have more than one entry and I need to display all the entries under that product code. ie

SS030 White £2.00
SS045 Red £1.75
SS055 Green £2.20
YH654 White £2.00
YH654 Purple £2.45
YH654 Yellow £3.65
TT101 Yellow £1.99
TT101 Green £3.67
TT102 White £2.67

If the user enters YH654 in Range A1 I would like to see the 3 entries one under each other on 'Sheet1' or if they use TT101 then I get the 2 entries.

I am sure you get what I mean and I believe that I understand the limitations of Vlookup so I am a little stuck on how best to approach this.

Hope you can assist, this is for the wife so I am really under pressure.
Cheers
Matt

 

[Brianwarnock]

Hi all

Hope you can assist, this is for the wife so I am really under pressure.
Cheers
Matt

This rings a bell, I feel sure that I have provided a solution to this before, no time to look now but maybe later.

Brian

 

[Brianwarnock]

Ok it wasn't you but somebody else under pressure from their wife for a similar but different requirement, here is the code I haven't time to modufy and test at the moment but the appraoch you require is similar so it should give you the idea

Brian

Sub copyrows()

' Copies a row from sheet2 to sheet3 where col D in that row is in Sheet1 Col D
' ie sheet1 Col D is a list of required values, unsorted
' B J Warnock

Dim lastrow1 As Long
Dim lastrow2 As Long
Dim rownum1 As Long
Dim rownum2 As Long

lastrow1 = Sheets("sheet1").Range("D65536").End(xlUp).Row
lastrow2 = Sheets("sheet2").Range("D65536").End(xlUp).Row

rownum2 = lastrow2

Do
    rownum1 = 1
    Do Until rownum1 > lastrow1
    If Sheets("sheet1").Cells(rownum1, 4) = Sheets("sheet2").Cells(rownum2, 4) Then
    Sheets("sheet2").Rows(rownum2).Copy
       With Worksheets("Sheet3")
        .Rows("1:1").Insert Shift:=xlDown
        .Range("A1").PasteSpecial
       End With
       Exit Do
    Else
    rownum1 = rownum1 + 1
    End If
    Loop
rownum2 = rownum2 - 1
Loop Until rownum2 = 0
Application.CutCopyMode = False

End Sub

 

[MGumbrell]

Thank you Brian.

Is there a none VBA solution that your aware of.

With Vlookup and Match and Countif and Address I have been able to:-

Locate the Lookup
Identify How many entries there are
The address of the first entry

What I am failing is being able to use this information to populate ranges with the number of rows that cotain the lookup rereference in column 1.

Sorry for not being specific in the first place.

Thank you
Matt

 

[Brianwarnock]

I always take the simple solution and use VBA, but NBVC might be able to help if he/she comes on, see his solution to Vlookup no VBA from a couple of weeks ago, I'd have never come up with that.

Brian

 

[NBVC]

There are a couple of ways...

1. You can use an array formula or you can use a helper column and regular formula.

The array formula way... If you have Excel 2003 or earlier, first do a countif, so, say in B1 of Sheet1, enter formula like:

=COUNTIF(Sheet2!A:A,A1)

this counts matches.

Then in Sheet1, say A2 enter:

=IF(ROWS($A$1:$A1)>$B$2,"",INDEX(Sheet2!$A$1:$A$100,SMALL(IF(Sheet2!$A$1:$A$100=$A$1,ROW(Sheet2!$A$1:$A$100)-ROW(Sheet2!$A$1)+1),ROWS($A$1:$A1))))

where A1:A100 is the range in Sheet2 where the lookup should find the matches. Adjust the range to match your data

Note: This formula is an array formula and must be confirmed by holding the CTRL and SHIFT keys down and then hitting ENTER. You should see { } brackets appear.

If you have Excel 2007 or later, you don't need the COUNTIF counter...

=IFERROR(INDEX(Sheet2!$A$1:$A$100,SMALL(IF(Sheet2!$A$1:$A$100=$A$1,ROW(Sheet2!$A$1:$A$100)-ROW(Sheet2!$A$1)+1),ROWS($A$1:$A1))),"")

again, confirm with CTRL+SHIFT+ENTER, then copy down as far as you want

To get the rest of the data, use INDEX/MATCH

=INDEX(Sheet2!B$1:B$100,MATCH($A2,Sheet2!$A$1:$A$100,0))

adjust ranges and copy down and across the columns.

2. The other way with helper column.

Assuming data starts in Row 2, A2, then in Sheet2, D2, enter:

=IF(A2=Sheet1!$A$1,COUNT(D$1:D1)+1,"")

copied down

In E2:

=MAX(D:D)

then in Sheet2, A2

=IF(ROWS($A$1:$A1)>Sheet2!$E$2,"",INDEX(Sheet2!A:A,MATCH(ROWS($A$1:$A1),Sheet2!$D:$D)))

copied down as far as you need and across.


Note: The array formula should not use whole column references or very large ranges...and should not be copied down many rows. If you have those constraints use the second method for better efficiency.

 

[MGumbrell]

Thank you very much Brian

So that I learn too, I will try both methods.

Regards
Matt

 

[Brianwarnock]

to help you start here is some working code running off a command button on sheet1

It does not clear any existing rows merely pushes down any existing data, this would allow your wife to "stack" requests, or she can delete existing stuff.

Happy learning

Brian