# Which Function for this excel pickle?

#### TommyBoy7

##### New Member
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
 Warehouse SKU Cost Cost Multiplier 5h 1343qq1 46.11 1.20 6d 1343qq1 45.00 1.20 33e tt661qw 11.14 1.15 12s tt661qw 11.14 1.20 12s 77r1343 13.22 1.20 5h 77r1343 13.22 1.20

<colgroup><col width="65" span="3" style="width: 65pt;"><col width="82" style="width: 82pt;"></colgroup><tbody>
</tbody>

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Michael M

##### Well-known Member
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

##### MrExcel MVP
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
Code:
``````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

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

Thanks for any help Michael!

#### Michael M

##### Well-known Member
Did you try the solution provided by Fazza first ???

Replies
1
Views
446
Replies
0
Views
410
Replies
5
Views
455
Replies
4
Views
582

1,196,027
Messages
6,012,953
Members
441,740
Latest member
abaz21

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