Cross-referencing and Looking Up

Greetings All,

I have read numerous threads on this site, and it seems that the people who respond are very knowledgeable. I have hit a huge road block and have no idea how to solve it.

Ok, I will try to explain this as best I can, and more than willing to post examples if need be. I am attempting to located the values in Col A, of spreadsheet 1, in Col A of spreadsheet 2. Now, sheet 1 is set up with row headers as well as col. In sheet 2, the headers (col and row) from 1, are side by side. What I need to do is find a way to count the instances that the col headings occur in sheet 2, and input that number in sheet 1.
Example:
Lots Receiving Complaints (2010) Counterfeit/Diverted Product Child Opened Damaged Coating
0013A61 3 2

0013A62 5

If this is unclear, I apologize. The above is an example of what I need sheet 1 to look like. Sheet 2 looks like this:
0013A61 Counterfeit/Diverted Product
0013A61 Counterfeit/Diverted Product
0013A61 Counterfeit/Diverted Product

I have tried doing this manually, but there are over 1800 rows I need to look-up, and it's taken my 3 days to get through only 200 of them. It's very time consuming, not to mention tedious. I was wondering if there were a way to speed up the process. Your help is greatly needed and very much appreciated!!!

Thanks in advance!!!!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!


By the way, have you tried a Pivot Table? or Sumproduct()

A can't seem to upload a sample right now..

But if you go to Sheet2 and then go to Insert tab and select Pivot Table

Accept the defaulted range if it is correct...

Then drag the Lot Number from top section to the Row Labels section below

drag the lvl 3 Cat from the top section to the Column Labels section below, then drag it again from the top to the Sigma Values section below.

You should get a pivot table of values come up.

To help me? I thought this was your project

If you need help though, then post a sample workbook with more variation in each of the columns.

Here is a sample.

I had to reduce your table to 500 rows.. too big (even zipped).

In the Pivot Table for 2007, you have option to filter out data...

In the sample I had attached there should be a drop down arrow in A4 and in B3,

You can filter (like AutoFilter) for wanted/unwanted data, etc.

Does that help?

Great! Glad that works out for you.

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

The other option is to group the original table so that the rows you want to include are at the top, then when you define the range for the Pivot table, include only those top rows that are relevant.