0

I have a large excel file where I have to do some data screening.

  • Lets say that Column A represent a car brand
    e.g. Toyota, Jaguar etc
  • Column B represents name of spare part
    e.g. Brake disks
  • Column C represents shelve in a ware house
    e.g. R10S12A

I want to screen the excel sheet to ensure that e.g. all brake disks for Toyota is stored on the same shelve. Basically I want to be able to flag any part that are stored over several locations i.e. for a given combination of values for column A and B there is more than one value in column C.

There are no free text fields to all naming will be consistent but there are 120,000 rows so rather large file.

Please note that e.g.
Toyota - Brakes - R10S12A
can share location with e.g.
Porsche - Clutch - R10S12A

This is OK, I just want to know if any give value in A&B (e.g. Porsche - Clutch) would return more than one value in column C.

Edit: Error code when trying suggested solution below:

enter image description here

1

you can use COUNTIFS for it:

=COUNTIFS(A:A,A2,B:B,B2,C:C,"<>"&C2)>0

enter image description here

  • Can you go into a little more detail on what is happening here? – Burgi Nov 14 '16 at 14:39
  • I get #Name? error. Not sure why. I use Excel 2010. I had to change "," to ";" as I am using european version. – CAX Nov 14 '16 at 14:51
  • @CAX: does it work with;? – Máté Juhász Nov 14 '16 at 15:30
  • My apologies, just changed workplace and I also had to change the COUNTIFS to a local language for the name of the formula. Now it works beautifully! Many thanks! – CAX Nov 14 '16 at 16:10
0

Use AutoFilter. With Autofilter you can select only the rows that match with your filter.

  1. Activate autofilter

enter image description here


  1. Click in the triangles to display a menu of options on each column.

enter image description here

  • How he can solve his specific problem with your suggestion? – Máté Juhász Nov 14 '16 at 16:46
  • I am reading again it now. I misunderstood the question. Sorry. – jcbermu Nov 15 '16 at 8:38

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.