Vlookup(?) based on group of values

Sample workbook.xlsx

Hi,

See sample workbook.

I have a list of names in column A. In column B there's numbers.

I want to list all names from column A that have the exact same numbers in column B.

In the sample workbook, apple and oranges contains the exact same numbers, even if they are not in the same order.
I want them listed below.

If i.e value in B16 is 15, then there's not 100% match and oranges will not be listed.

In my list theres hundreds of names, so having a formula with specific names in it will be a lot of work.

My problem is a bit tricky to explain, so please ask if it is difficult to understand.


Thanks.

Windows 7
Excel 2013

In your example, if B16=15, neither oranges nor apples would be shown as you want 100% match i.e. same count of number of apples and oranges with same corresponding values (?).

Originally Posted by JohnTopley

In your example, if B16=15, neither oranges nor apples would be shown as you want 100% match i.e. same count of number of apples and oranges with same corresponding values (?).

That's correct. It has to be 100% match, else nothing will be shown.

In my actual workbook though, I have car names in column A and parts in column B.
I want to list all cars that have the exact same parts in column B.

Then I can conclude that every car that will be listed is the exact same car, just with different names.

I've added a new sample: sample2.xlsx

Here BMW and Audi are 100% match and I want them listed.
VW is also a match but include 1 more part. If possible, I want this listed as well with a different color or something to show that it is the same car, with 1 or more parts.

I have put together a possible VBA solution which requires data be sorted by Make then Parts. At the moment it only deals with 100% matches

Two lists are created using formulae in Columns C and D

In C to get unique list of Makes

=IFERROR(INDEX($A$2:$A$501,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$501),0,0),0)),"")

in D to get count of Parts for each Make

=IF($C2="","",COUNTIF($A$2:$A$501,$C2))

Please Login or Register  to view this content.

Thanks,

I get an error in this line in macro: m1 = WorksheetFunction.Match(Make(r, 1), Range("Make"), 0) - 1 '

My error: change named range "Models" to "Make": I obviously changed the code but not the named range

It worked in some of them, but not all.

Wish I could post my actual sheet, but it's kind of confidential.

I have some blanks in column B where there is text in column A, and vice versa. Could that be an issue?

Edit: Double post

Remember data needs to be sorted by "Make" (A) and then "Parts" (B): macro will not work otherwise.

Yes, macro runs fine and everythings looks good, but when the parts doesn't match when I manually check it by filtering the matches.

Not sure what you mean by

when the parts doesn't match when I manually check it by filtering the matches.

is it related to this ???

At the moment it only deals with 100% matches

Post file illustrating problem.

See attached: hopefully this gives you the results in your actual list.

Please Login or Register  to view this content.

Hi again,

Thanks.

I get an error here (Subscript out of range):

Please Login or Register  to view this content.

Do you know why?

Cannot diagnose without data. This is an issue when we are presented with a small sample of (usually simplified) data as we cannot "stress" test the code..