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!
Last edited by mudraker; 03-14-2009 at 06:15 AM. Reason: Mark as Solved as per OP request
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.
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
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 #.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Re: comparison of two spreadsheets
For Mewingkitty, thanks for your reply. Unfortunately, we found out that we might be duplicating records. We located some of them and deleted them but in the future we might get repeated data again. In that case the formula would show an error in the cell right?
For JBeaucaire, your formula sounds like this is it!!!!!!!! thank you so much, I will try it and come back to see if it worked. You have no idea the headaches that I am getting with this reports.... I will get back to you once I try it on Monday.
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.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
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".....
Last edited by best401; 03-07-2009 at 10:23 PM.
xxxxxxxxxxxx
Last edited by best401; 03-07-2009 at 10:25 PM.
Feel fee to post up sample workbook(s) to show us any further issues you're having.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank you so much for help. I'm afraid it did not work today... I attached two spreadsheets that I made quickly here, the raw data and the report on when I need the formula.
No attachments.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
************
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")
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
I think I will be able to do it finally. I really appreciate so much your help. This will save me a whole afternoon once a month. Thank you!
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]
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi, Looks like it is too late for me to change it... who is a moderator? I do not know how to recognize them.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks