Combining data from two different spreadsheets

[Vento9]

Hello all!

Looking for some help combining data from two separate sheets all in the same workbook using Excel 2013.
I have a master sheet which contains a list of donor information that I need consolidated with separate monthly sheets.

The master sheet contains Donor ID, EIN, Organization Name, Address etc.

The monthly sheets contain the Donor ID and Donor Name.

I guess the piece that's stumping me is that some donors may have multiple organizations listed.
For instance:

Master List

Donor ID | EIN | Org. Name | Address
1009 999999999 ABC 123 Any street
1021 111111111 DEF 456 Elm st
1021 222222222 ASD 525 Cherry Ln
1231 555555555 ESG 364 Maple Way


January

Donor ID Donor Name
1009 Stern Howard
1021 Mahoffer Jack
1231 Nilus Darth


I need the EIN, Org Name and Address data from the master list to combine with the January information. I also need to show if a donor has multiple organizations.

I hope this makes some sort of sense. I start confusing myself if I stare at it for too long.


Thanks for any suggestions!

 

[RudiS]

It would be much simpler to combine Donor Name to the Master Sheet (using VLOOKUP) than to write multiple formulas in the January sheet to bring in the three pieces of info. This way, you only write one formula (to bring the name in) and after that you can highlight the duplicate Donor ID's with conditional formatting on the Master.

 

[Vento9]

It would be much simpler to combine Donor Name to the Master Sheet (using VLOOKUP) than to write multiple formulas in the January sheet to bring in the three pieces of info. This way, you only write one formula (to bring the name in) and after that you can highlight the duplicate Donor ID's with conditional formatting on the Master.

Thanks for the response.

I'm having a little trouble getting Vlookup to populate the data I'm looking for.

What I've done is moved the donor id, last and first name to the master file. I basically need to run something that compares column A and B (Donor Id master and Donor ID January) so it inserts spaces in column B to account for multiple donor Id's listed in column A.

The master file contains multiple donor Id's that correlate with different organizations.


Thanks

 

[RudiS]

In your OP I see there is a Donor ID 1021 which appears twice in the Master and once in the Name list. If you have a VLOOKUP in the Master sheet in Column E for example, you can bring "Mahoffer Jack" into the Master based on the ID.

I don't quite understand the idea of creating spaces to accommodate extra data. Functions cannot do this anyhow!

 

[Vento9]

Can you give me an idea of what that would look like ?

 

[RudiS]

Sure...

Master Sheet:
Including finding duplicate ID's using conditional formatting...

Excel 2012
	A	B	C	D	E	F
1	Donor ID	EIN	Org. Name	Address	Name
2	1009	999999999	ABC	123 Any street	Stern Howard
3	1021	111111111	DEF	456 Elm st	Mahoffer Jack
4	1021	222222222	ASD	525 Cherry Ln	Mahoffer Jack
5	1231	555555555	ESG	364 Maple Way	Nilus Darth
6
Master

Cell Formulas
Range	Formula
E2	=VLOOKUP(A2,January!$A$2:$B$4,2,0)

January Sheet:

Excel 2012
	A	B	C
1	Donor ID	Donor Name
2	1009	Stern Howard
3	1021	Mahoffer Jack
4	1231	Nilus Darth
5
January