I need to combine data from 2 spreadsheets


L

Lisa Barr

I'm not good with macros, so if we could do this simply, I would
appreciate it.

Spreadsheet 1
I have a list of all my accounts
The headings I need from that spreadsheet are:

Column A id
Column B name
Column C billing_address_street
Column D billing_address_city
Column E billing_address_state
Column F billing_address_postalcode
Column G billing_address_country

Spreadsheet #2
This is a list of registration numbers

Column A R/N
Column B Product
Column C Account
Column D ID

The ID Heading on Spreadsheet 2 is the same as 1 entry in the ID
heading on Spreadsheet 1

What I want to do is add information from SS1 to SS2

Like this

I want to start on SS2. Cell D2 contains information
"1008e000-000e-0000-0000"

If I were to go to SS1 and do Edit Find and entered this information
in the lookup blank, it returns A692

B692 contains the name of the business
C692 contains the street address
D692 contains the city
e692 state
f692 zip
g692 country

I want to take the information containted on SS2 cell 2d
find the corresponding information on SS1
copy the information on cells b-c-d-e-f-g of that row and paste it
into SS2

Copy SS1 cell b692 and past into SS2 cell 2e
Copy SS1 cell c692 and past into SS2 cell 2f
Copy SS1 cell d692 and past into SS2 cell 2g
Copy SS1 cell e692 and past into SS2 cell 2h
Copy SS1 cell f692 and past into SS2 cell 2i
Copy SS1 cell g692 and past into SS2 cell 2j

Damn, I know this is difficult to understand, please ask questions.

I want the final product to be this
Column A R/N
Column B Product
Column C Account
Column D ID
Column E name
Column F billing_address_street
Column G billing_address_city
Column H billing_address_state
Column I billing_address_postalcode
Column J billing_address_country

Please help. I know there must be a function somewhere that will do
it


In case there are limits to Excel.

SS1 has 32,291 rows

SS2 only has 2520

Thanks in advance

Lisa Barr
 
Ad

Advertisements

R

Rodrigo Ferreira

You don't need a macro. You can use VLOOKUP function. Do you know how to use
VLOOKUP function?

Try something like this
Sort Sheet1 by Id (Column D)

On Cell E3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 2, false )
F3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 3, false )
G3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 4, false )
H3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 5, false )
I3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 6, false )
J3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 7, false )

Copy this functions to others rows
 
Ad

Advertisements

L

Lisa Barr

You don't need a macro. You can use VLOOKUP function. Do you know how to use
VLOOKUP function?

Try something like this
Sort Sheet1 by Id (Column D)

On Cell E3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 2, false )
F3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 3, false )
G3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 4, false )
H3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 5, false )
I3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 6, false )
J3:
= VLOOKUP( D3, Sheet1!$A$1:$G$32291, 7, false )

Copy this functions to others rows

Damn. That was slick!

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top