Excel gurus please

Django x2

Django x2

Django x2

Soldato
Joined
28 Sep 2008
Posts
14,207
Location
Britain
Ok, I did something like this 3 years ago and for some reason my mind has gone blank. All I remember is I ran a macro to kick it all off!

So, I have one Excel workbook with 3 sheets. two sheets are populated with different information, one sheet is blank (sheet 3).

The two populated sheets have some information in them which is true of both sheet 1 and sheet 2 (such as company name). So, to explain, sheet one might have "company a, company c, company e" in it and sheet two might have "company a, company b, company c, company d and company e" in it.

Also within sheet two is further information not present in sheet one.

I want to perform a task that name matches across the two sheets and if it spots a match it outputs the company name and the extra data from column c over to sheet 3.

Does that really make sense? I hope so.

EDIT: I'm going to explain better if I can:

Sheet 1

Column A
Company a
Company c
Company e
Company g

Sheet 2
Column A Column C
Company a John
Company b Dave
Company c Mike
Company d Jeff
Company e Will
Company f Jane
Company g Bob

Run the macro and you get

Sheet 3
Column A
Company a John
Company c Mike
Company e Will
Company g Bob

Clear??? :confused:
 
Last edited:
Didn't quite catch all that, but are you thinking of the vlookup function? Sounds like you'll need that at some point at least.
 
From the example you've given, get the company list on sheet three (copy via a macro), then use vlookup in the next column.

=vlookup(a1,sheet2!$range,2, false)
 
Dolph said:
From the example you've given, get the company list on sheet three (copy via a macro), then use vlookup in the next column.

=vlookup(a1,sheet2!$range,2, false)
Click to expand...

Thanks Dolph, doesn't seem to work though. Whats the '2' part and the 'false'

I have entered all the companies from sheet 1 into column A of sheet 3. The companies in sheet 2 are in column B and the information I want pulled from sheet 2 is in column E.

So, in sheet 3, I want column A to remain the same as I what I pasted, column B displays the same company name matched from sheet 2, and column C will show the information for the matched companies in column C.
 
the 2 is the number of columns between the cell that contains the lookup information, and the cell that contains the data you want to display. If you're going from B to E, the 2 should be a 4.

The false part ensures it doesn't attempt a range lookup, but looks for an exact match.

On sheet 3, you will want two vlookups, one in column B to match the company name, and one in column C to display the information from sheet 2 column e.

You also need to insert the cell range where I put range above (from your description, it will be Bx:Ey where x is the first row number and y is the last).

Hope that helps.
 
Dolph said:
the 2 is the number of columns between the cell that contains the lookup information, and the cell that contains the data you want to display. If you're going from B to E, the 2 should be a 4.

The false part ensures it doesn't attempt a range lookup, but looks for an exact match.

On sheet 3, you will want two vlookups, one in column B to match the company name, and one in column C to display the information from sheet 2 column e.

You also need to insert the cell range where I put range above (from your description, it will be Bx:Ey where x is the first row number and y is the last).

Hope that helps.
Click to expand...


It does Dolph thanks. You say I need two vlookups, can you let me know what they actually are (in addition to the one above I mean).

EDIT: so I have

=VLOOKUP(A1,Sheet2!$B3:E3790,4, FALSE) entered into cell B1 of sheet 3. Cell A1 down the way has the company names from sheet 1 in. B3 is the first company name in sheet2 and E3790 is the last cell of the data I want to pull in column E (there are 3790 companies in sheet 2). Oh, whats the A1 bit at the start?
 
Last edited:
ok , got it to work. Unfortunately I've just noticed that not all of the company names between the two sheets are the same which means I'll have to go through some of them manually


FAIL
 
You must log in or register to reply here.
Back
Top Bottom