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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Colour[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]01/01/2011[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]01/01/2011[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]01/01/2011[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]02/01/2011[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]03/01/2011[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]04/01/2011[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]04/01/2011[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]05/01/2011[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]05/01/2011[/TD]
[/TR]
</tbody>[/TABLE]


Second sheet looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Colour[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Black[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]White[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Purple[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Colour[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Black[/TD]
[TD]01/01/2011[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]White[/TD]
[TD]02/01/2011[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue[/TD]
[TD]03/01/2011[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Red[/TD]
[TD]04/01/2011[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Purple[/TD]
[TD]05/01/2011[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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