PDA

View Full Version : Any Excel formula pros out there?


joelogic
08-31-2010, 2:42 PM
I want to compare similar cells by row and column in two worksheets and if the values are the same then I want to pull data from that row and add it to the other worksheet. If someone can help me it would be appreciated and I will give further details and clarification.

Thanks.

Example workbook. If A1 of worksheet B is equal to A1 of worksheet A then copy E1/F1 from worksheet B to E1/F1 of worksheet A. And then repeat down each row until the end.

Corbin Dallas
08-31-2010, 4:30 PM
You need to combine all the data in one workbook. You can have seperate worksheets, but two different workbooks causes issues.

Why not port the workbooks to Access and combine?

BTW, how much data are we talking about?

nishanh
08-31-2010, 4:53 PM
I believe you can reference cells in another workbook by using this syntax:

='[MyWorkbook.xlsx]Sheet1'!A5

I'm assuming you're using Excel 2007. For earlier versions, the filename should end in 'xls' rather than 'xlsx'.

DavidRSA
08-31-2010, 6:21 PM
Wouldn't this be achieved by a simple vlookup?

OliveCookieMonsta
08-31-2010, 6:51 PM
=Vlookup(cell to match,other worksheet group of cells,column number with info, 2)

joelogic
08-31-2010, 10:59 PM
Thanks for the fast responses. Simple vlookup. Simple to you maybe ;).

Sorry, I meant worksheets.
Data can range from 300 rows to 5000 rows.
When using vlookup how can I compare two cells that are almost the same.
Comparing A1 for worksheet A (100 Main St.) to A1 of worksheet B (100 Main St. #100)

joelogic
08-31-2010, 11:07 PM
=Vlookup(cell to match,other worksheet group of cells,column number with info, 2)


Could you please give the example again but using the syntax?

Worksheet A
Worksheet B

Column A Worksheet A has the data I want to match to Column A Worksheet B.
Will the formula search Column A Worksheet B for a similar match? If there is a match then I want to copy Column E and F of that row on Worksheet A to the matched row on Worksheet B.

thedonger
08-31-2010, 11:12 PM
Vlookup in NOT what you are looking for!

You need to do an if() formula.

If(condition,then,else)

=if(a=b,c,d)

joelogic
08-31-2010, 11:15 PM
How would I write =if(A1 of worksheet A = anything between A1:A1000 of worksheet B, copy E1 and F1 to the match on worksheet B)?

thedonger
08-31-2010, 11:16 PM
If you want to compare cells that are almost the same you might not be able to use a formula, might need to be done manual, but you can put all the data on one tab and sort so it can be done faster.

joelogic
08-31-2010, 11:19 PM
If I had to do it manually it would make it not worth doing.

Dr Rockso
08-31-2010, 11:24 PM
How would I write =if(A1 of worksheet A = anything between A1:A1000 of worksheet B, copy E1 and F1 to the match on worksheet B)?

You're going to want to nest an IF( ISERROR( and MATCH(

Something like

IF(ISERROR(MATCH(A1,A1:A1000,0)),"",E1)
IF(ISERROR(MATCH(A1,A1:A1000,0)),"",F1)

and put them in their respective columns. The way it's written above with the "" will leave a blank cell when there isn't a match. I don't remember the syntax for working in multiple workbooks, but I think you already have that answer to your question.

wildcard
09-01-2010, 3:18 PM
This is one of those things that are easy to do.. but a real pain in the butt to explain since it can't be done with just a formula. More like 2 formulas and a sort.

"Vlookup" so that you have the data on a single sheet.
Compare the values using an "if" statement. Returns a certain value if they match or don't.
Sort the list so that whatever doesn't match floats to the top.
Done.