database - Combine separate spreadsheets into one master sheet in Excel? -
i need combine 3 excel spreadsheets 1 "master" sheet quickly.
the first sheet has following columns:
- customer (company name)
- telephone
- fax
- contact
- contact's telephone
- contact's fax
- contact's email
the second sheet has following columns:
- customer (company name)
- delivery street
- delivery country
- delivery zip
the last sheet has following columns:
- customer (company name)
- sales representative
i tasked combining above information 1 excel sheet within next few hours. if customer lists identical copy columns , call day. sheets have duplicates, , in 3 sheets customers not identical. need "master" set of data following columns:
- customer id (auto-assign)
- customer
- sales representative
- contact
- contact email
- contact telephone
- contact fax
- company telephone
- company email
- delivery info
i have excel 2013, if helps.
you copy customer (company name) lists single sheet , perform remove duplicates , use 'master' sheet column b.
the customer id fill down, type 1 in cell a2 , 2 in cell a3 , select cells a2 , a3 , double-click lower-right corner of selection have autofill down last company name in column b.
then, rest appropriate vlookup formulas. example, in cell sheet 'master' c2 (sales representative), formula be:
=iferror(vlookup($b2,'sheet3'!$a:$b,2,false),"")
and in sheet 'master' cell d2 (contact), formula be:
=iferror(vlookup($b2,'sheet1'!$a:$h,5,false),"")
Comments
Post a Comment