Need to Match columns

[marshak]

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?

THANKS SO MUCH in advance!

 

[MUKESHY12390]

perhaps this ...

=IF(OR(K2=0,K2=10),"Match found","Not found")

 

[marshak]

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

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.excelforum.com/excel-formulas-and-functions/1239677-need-to-compare-columns-and-satisfy-a-specific-argument.html

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.

 

[marshak]

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

 

[marshak]

Hi all,

I am still trying to work through this issue. Any help is greatly appreciated.

Cross posted at: https://www.excelforum.com/excel-for...-argument.html

 

[sandy666]

If you are allowed to use PowerQuery (PC: 2010/2013 add-in, 2016 and above built-in) try:

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"

[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #5B9BD5"]CUSTNMBR
[/TD]
[TD="bgcolor: #5B9BD5"]UNITPRCE
[/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"]CUSTNMBR
[/TD]
[TD="bgcolor: #70AD47"]UNITPRCE
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]

569845​

[/TD]
[TD="bgcolor: #DDEBF7"]

7​

[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]

213655​

[/TD]
[TD="bgcolor: #E2EFDA"]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFF00"]

213655​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

213655​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFC000"]

565975​

[/TD]
[TD="bgcolor: #DDEBF7"]

10​

[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]

213655​

[/TD]
[TD="bgcolor: #E2EFDA"]

0​

[/TD]
[/TR]
[TR]
[TD]

255463​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]

565975​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]

213655​

[/TD]
[TD="bgcolor: #DDEBF7"]

12​

[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]

565975​

[/TD]
[TD="bgcolor: #E2EFDA"]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFC000"]

565975​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]

569845​

[/TD]
[TD="bgcolor: #DDEBF7"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

569845​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFF00"]

213655​

[/TD]
[TD="bgcolor: #DDEBF7"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

565975​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]

255463​

[/TD]
[TD="bgcolor: #DDEBF7"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFF00"]

213655​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

or if you need customer only

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]CUSTNMBR[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

213655​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

565975​

[/td][/tr]
[/table]

[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]

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

 

[sandy666]

You are welcome