comparison of two spreadsheets

Hi All,
I hope I am clear on what I need. I have two spreadsheets, The spreadsheet #1 has the information of two years and the Spreadsheet #2 is a montly report. The Spreadsheet # 1 hasta on the column A a number of transaction, and at the column Q the invoice number. The Spreadsheet # 2, only has in the column A the Invoice number. I need to find in the Spreadsheet # 1 the Invoice numbers that match the Invoices that I have in the Spreadsheet # 2 by bringin the "Number of Transaction".
For example:
Spreadsheet 1: Has on column A3 the Number of Transaction # 0123, and at the column Q3 the Invoice number 555. At the Spreadsheet number # 2 I have the INvoice # 555 located on A10. I need to know what is the transaction # by adding a formula in a new column (G) so I want to have the Transaction # 0123 in the new column added G.
If I do this manually it will take me hours since these report and the Master is so big, and right now I am doin it using Ctrl-F

I hope anyone can help me.... I got lots of grey hair thinking on the possible formula with no results... Thank you!

Will there be more than one occurance of the same invoice number on a sheet? Or a maximum of one listing of each transaction number per sheet.

This is a standard INDEX/MATCH formula. On your second sheet, try something like this:

=INDEX(Sheet1!$A$1:$A$1000,MATCH(Sheet2!A10,Sheet1!$Q$1:$Q$1000,0))

That will spot the invoice # you place in A10 over on the first sheet in column Q with the MATCH() formula, then bring over the matching INDEXed transaction #.

My suggested formula won't point out to you when there is duplication in the raw data. It will simply find the first match and bring back the associated data.

If you want to put in an additional test that would flag to you when there are duplicated values in your source data, you can do it a couple of ways. One way is to COUNTIF how many times the value in A10 appears in column Q and tell you if it's more than 1...like this:

=COUNTIF(Sheet1!$Q$1:$Q$1000,Sheet2!A10)

The answer should always be 1. If it's 2 or more, there's a problem. You could nest that into an IF statement like so:

=IF(COUNTIF(Sheet1!$Q$1:$Q$1000,Sheet2!A10)>1,"Duplicated","OK")

You can do the same thing with Conditional Formatting, actually making cell A10 itself change color if it appears more than twice in the source sheet...that's a little more involved though, so I won't go into it unless you decide that's the way to go. It would be easier to just make the formula above light up the cell if the value "Duplicated" occurs to help draw attention.

JBeaucaire, I could not wait until I was back to the office on Monday so I created a little spreadsheet to try this formula. There was something that I did not explain myself correctly, and it that as per what I saw in your formula, states the whatever is at the cell A10, but it is not... is any invoice number that match from the sheet 1, and sheet 2, so I modified your formula AND IT WORKED!!!!!!!... This is what I did:

=INDEX(Sheet1!$A$1:$A$1000,MATCH(Sheet2!$A$1:$A$1000,Sheet1!$Q$1:$Q$1000,0))

I did this manually and it took me about four hours, but now, I have to run this report monthly and I just couldn't do it manually anymore because Excel has the option to make it easy, I just did not now where to go for help.

Thank you so much for your help...

Now to see if we duplicated data, I do not need to see it in both spreadsheets since the Sheet # 2 is a report from a Program that would not bring me duplicated information, I just run it from "Aria" and export it to excel. But YES I need to see if we are duplicating from the "RAW DATA" (or what I named "sheet #1). I tried the other formula that you gave me to find out duplicated records.... I changed to this:

=IF(COUNTIF(Sheet1!$Q$1:$Q$1000,Sheet1!$Q$1:$Q$1000)>1,"Duplicated","OK")

IT WORKED! .... but.... to see which ones have the cell with the word "duplicated" I would have to scroll down across the whole spreadsheet OR use "Data" "Filter" and select "duplicated".... but here is the other issue that I have and that I posted yesterday also. Somehow, the List that displays from the "Filter" is showing the reconrds until the row 1927. The records entered from the row 1928 and up are not being read by "Filter".....

xxxxxxxxxxxx

Feel fee to post up sample workbook(s) to show us any further issues you're having.

No attachments.

Placed in D2 and copied down:

=INDEX('[Raw data.xls]Sheet1'!$A:$A,MATCH(A8,'[Raw data.xls]Sheet1'!$D:$D,0))

Or, to not have "#N/A" errors from missing data:

=IF(ISNUMBER(MATCH(A2,'[Raw data.xls]Sheet1'!$D:$D,0)),INDEX('[Raw data.xls]Sheet1'!$A:$A,MATCH(A2,'[Raw data.xls]Sheet1'!$D:$D,0)),"No match")

Well, that's good to hear!

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

best401

For a list of Administators & Moderators follow the link at the Forum rules - rule 11

A link to the forum rules is in the top 1/3 of the screen

I have marked your thread as Solved