# Need to Match columns

#### marshak

##### Board Regular
Hi all,

I have a very large spreadsheet (7000+ rows).

Column B contains various Customer Names that repeat (column name: CUSTNMBR)

Column K contains the Unit Price of each invoice (column name: UNITPRCE)

I need to know which customers show a unit price of \$0.00 AND \$10.00.

Can anyone help me come up with a formula or function to achieve this?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

perhaps this ...

#### marshak

##### Board Regular
Thanks so much for your reply. What I need the formula to do is...single out those customers that have a unit price of 0.00 in at least one of the rows in Column K AND 10.00 in at least one of the rows in Column K.

So say I have the following data: I need to know that Cust# 213655 shows both a unit price of 0.00 and 10.00

CUSTNMBR UNITPRCE
569845 7.00
213655 0.00
565975 10.00
255463 8.00
213655 12.00
565975 0.00
569845 12.00
569845 10.00
213655 10.00
565975 1.00
255463 10.00
213655 0.00

#### marshak

##### Board Regular
Thanks so much! I will cross post from now on.

#### sandy666

##### Banned - Rules violations
If you are allowed to use PowerQuery (PC: 2010/2013 add-in, 2016 and above built-in) try:
Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CUSTNMBR", Int64.Type}, {"UNITPRCE", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([UNITPRCE] = 0 or [UNITPRCE] = 10)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"CUSTNMBR", Order.Ascending}}),
#"Kept Duplicates" = let columnNames = {"CUSTNMBR"}, addCount = Table.Group(#"Sorted Rows", columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(#"Sorted Rows", columnNames, removeCount, columnNames, JoinKind.Inner)
in
#"Kept Duplicates"``````

 CUSTNMBR UNITPRCE CUSTNMBR UNITPRCE 569845​ 7​ 213655​ 0​ 213655​ 0​ 213655​ 10​ 565975​ 10​ 213655​ 0​ 255463​ 8​ 565975​ 0​ 213655​ 12​ 565975​ 10​ 565975​ 0​ 569845​ 12​ 569845​ 10​ 213655​ 10​ 565975​ 1​ 255463​ 10​ 213655​ 0​

<tbody>
</tbody>

Last edited:

#### sandy666

##### Banned - Rules violations
or if you need customer only

 CUSTNMBR 213655​ 565975​

Code:
``````[SIZE=1]let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CUSTNMBR", Int64.Type}, {"UNITPRCE", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([UNITPRCE] = 0 or [UNITPRCE] = 10)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"CUSTNMBR", Order.Ascending}}),
#"Kept Duplicates" = let columnNames = {"CUSTNMBR"}, addCount = Table.Group(#"Sorted Rows", columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(#"Sorted Rows", columnNames, removeCount, columnNames, JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Kept Duplicates",{"UNITPRCE"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
#"Removed Duplicates"[/SIZE]``````

#### marshak

##### Board Regular
You are so Awesome! This is exactly what I needed! Thanks a bunch!

You are welcome

Replies
0
Views
330
Replies
0
Views
98
Replies
0
Views
116
Replies
6
Views
154
Replies
0
Views
60

1,171,631
Messages
5,876,552
Members
433,200
Latest member
CoolBlue_

### 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.

### Which adblocker are you using?

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

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