Not even sure how to explain this in a subject request

[ayeready]

Hi

Sorry for the terrible header but i'm not sure how to explain this correctly.

On one sheet I have 1 column of numbers. Each number is on a row with further data. Some columns have no data.
Some numbers are duplicated in the first column.

On a second sheet i have the same list of numbers with duplicates removed. The column headers are the same but the blank columns from sheet 1 are filled on sheet 2.

I need the second sheet to show the same spacing as the first in order to fill the blank values from sheet 1 using the known values from sheet 2 while setting the correct rows to mirror the duplicate numbers.

First sheet looks like this:

Number	Colour	Date
1		01/01/2011
1		01/01/2011
1		01/01/2011
2		02/01/2011
3		03/01/2011
4		04/01/2011
4		04/01/2011
5		05/01/2011
5		05/01/2011

<tbody>
</tbody>

Second sheet looks like this:

Number	Colour	Date
1	Black
2	White
3	Blue
4	Red
5	Purple

<tbody>
</tbody>

I need the second sheet to end up like this in order that i can then copy down each row in order to duplicate the rows in sheet 1 with all data from both sheets.

Number	Colour	Date
1	Black	01/01/2011
2	White	02/01/2011
3	Blue	03/01/2011
4	Red	04/01/2011
5	Purple	05/01/2011

<tbody>
</tbody>

Is this possible?

 

[ayeready]

In that case put this in B2 on sheet1 & fill down
=INDEX(Sheet2!$B$2:$B$6,MATCH(A2,Sheet2!$A$2:$A$6,0))

Outstanding Fluff. ray:

Just updated 2500 lines of data in seconds when it had previously taken half a day to do it manually.

Appreciate your help on this as had been banging my head against a brick wall looking at this the wrong way.

Off to fill another 2500 lines and have a tea to celebrate!

 

[Fluff]

Glad to help & thanks for the feedback