inserting cells in excel

[lisa17]

Hello,

We have 2 spreadsheets that we've brought into 1. The 1st half shows stock items with a transaction reference number. The 2nd half shows invoices with the same trans ref number. But as you can have more than 1 stock item per invoice, the 1st half has 8000 lines, 2nd half about 5000! So we need to insert cells in the 2nd half so that the reference numbers match up! e.g.
1st half 2nd half
123 123
123 234
234 345
345 456
345
345
456

so have to insert cells in the 2nd half so looks like this

123 123
123
234 234
345 345
345
345
456 456

it has 16 columns to the 1st half, and 13 columns in the 2nd half - so we're comparing the invoice numbers and highlighting the 13 columns in the 2nd half, right clicking and inserting cells, shifting them down!
It's taking forever - there's got to be a quicker way! Please help!

 

[01Patrik]

If the transaction reference number is the same, then there is no reason for you not to use the VLOOKUP function.

Say 1st half goes like this:

A
1 123
2
3


Say 2nd half goes like this:

... N O
1 ... 123
2 ...
3 ...


Then place the following function in 2nd half, in 'O1' in this example:

=vlookup($N1,'1st half'!$A:$P,2,FALSE)

where 2 is the number representing the second column in the reference A. This txample will return the value of column B in the row.

This function can be filled down to last row, and copied right in 16 columns - what you have to do when copying right is change the 2 figure representing the column B.

Example:

O1: =vlookup($N1,'1st half'!$A:$P,2,FALSE)
02: =vlookup($N2,'1st half'!$A:$P,2,FALSE)
P1: =vlookup($N1,'1st half'!$A:$P,3,FALSE)
P2: =vlookup($N2,'1st half'!$A:$P,3,FALSE)

etc.

Good luck



// Patrik

 

[01Patrik]

for more info about VLOOKUP, see faq68-4743



// Patrik