Which Function for this excel pickle?

[TommyBoy7]

I've got a spreadsheet with nearly a million rows, I've included a snippet below.
Each SKU should have the same cost regardless of warehouse. Also, each SKU should have the same cost multiplier regardless of warehouse. I need to find SKU's which do not abide by this.

So, SKU 1343qq1 should be identified as one that needs to be addressed in the system. Also, SKU tt661qw should also be identified since the cost multiplier is not the same. SKU 77r1343 is fine.

Any help is much appreciated!TommyBoy
[TABLE="width: 277"]
<colgroup><col width="65" span="3" style="width: 65pt;"><col width="82" style="width: 82pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 65, align: center"]

Warehouse[/TD]
[TD="class: xl63, width: 65, align: center"]SKU[/TD]
[TD="class: xl63, width: 65, align: center"]Cost[/TD]
[TD="class: xl63, width: 82, align: center"]Cost Multiplier[/TD]
[/TR]
[TR]
[TD]5h[/TD]
[TD]1343qq1[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"] 46.11 [/TD]
[TD="class: xl64, align: right"] 1.20 [/TD]
[/TR]
[TR]
[TD]6d[/TD]
[TD]1343qq1[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"] 45.00 [/TD]
[TD="class: xl64, align: right"] 1.20 [/TD]
[/TR]
[TR]
[TD]33e[/TD]
[TD]tt661qw[/TD]
[TD="class: xl64, align: right"] 11.14 [/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"] 1.15 [/TD]
[/TR]
[TR]
[TD]12s[/TD]
[TD]tt661qw[/TD]
[TD="class: xl64, align: right"] 11.14 [/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"] 1.20 [/TD]
[/TR]
[TR]
[TD]12s[/TD]
[TD]77r1343[/TD]
[TD="class: xl64, align: right"] 13.22 [/TD]
[TD="class: xl64, align: right"] 1.20 [/TD]
[/TR]
[TR]
[TD]5h[/TD]
[TD]77r1343[/TD]
[TD="class: xl64, align: right"] 13.22 [/TD]
[TD="class: xl64, align: right"] 1.20 [/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Is the data sorted by SKU ?
How do you ID which one needs to be addressed out of the 2 SKU's 1343qq1 ?
How do you want them identified ???
I'm guessing you'll need VBA for this many rows.

 

[Fazza]

Suggest you give the data a simple defined name, such as YourTable, then save the file & create a query.

Such as via ALT-D-D-N and follow the wizard. At the last step of the wizard, choose the option to edit in MS Query, and change the SQL to

SELECT A.SKU
FROM (SELECT DISTINCT SKU, Cost, [Cost Multiplier]
FROM YourTable) A
GROUP BY A.SKU
HAVING COUNT(*) > 1

How to manually (non VBA) create a query
https://analystcave.com/create-microsoft-query-excel-excel-query/

Or some other implementation of a query in your Excel version. Google can find explanations.

Query tables refresh like pivot table. Should be fast even on ~ one million data rows.

regards, Fazza

 

[TommyBoy7]

Is the data sorted by SKU ?

Yes, I have sorted by SKU.
How do you ID which one needs to be addressed out of the 2 SKU's 1343qq1 ?

I just need to know that this SKU needs addressing. Which cost is correct will be determined in a later step.

How do you want them identified ???

This doesn't really matter, just need to know which SKU's need attention.
I'm guessing you'll need VBA for this many rows.

I had to google VBA

Thanks for any help Michael!

 

[Michael M]

Did you try the solution provided by Fazza first ???