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?

 

[Fluff]

How about

Book1
	A	B	C
1	Number	Colour	Date
2	1	Black	01/01/2011
3	2	White	02/01/2011
4	3	Blue	03/01/2011
5	4	Red	04/01/2011
6	5	Purple	05/01/2011
Sheet2

Cell Formulas
Range	Formula
C2	=INDEX(Sheet1!$C$2:$C$10,MATCH(A2,Sheet1!$A$2:$A$10,0))

 

[ayeready]

The starting point is 2 sheets with the same columns but a different amount of rows on sheet 1 that needs to be mirrored on sheet 2 with the data in columns from sheet 1 transferred to sheet 2.

Don't think your solution works.

 

[Fluff]

In what way doesn't it work? It looks to give the same output as you showed.

 

[ayeready]

In what way doesn't it work? It looks to give the same output as you showed.

You end up with the same amount of lines in sheet 2 rather than the line count from sheet 1.

Apologies. I may be setting your solution up incorrectly.

 

[Fluff]

A formula cannot "Insert" rows.
Are you simply trying to get the colours from sheet 2 onto sheet1?

 

[ayeready]

A formula cannot "Insert" rows.
Are you simply trying to get the colours from sheet 2 onto sheet1?

Thanks.

I need the data from the columns but also the additional rows added to sheet 2.
If there's no possibility to structure the rows then it may not be solvable.

 

[Fluff]

Can you please explain what you need as an end result, forget any intermediate steps.

 

[ayeready]

Can you please explain what you need as an end result, forget any intermediate steps.

Hi Fluff

I would be happy with either of these two options as the final result.

[TABLE="class: grid, width: 500, align: left"]
<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]1[/TD]
[TD]Black[/TD]
[TD]01/01/2011[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Black[/TD]
[TD]01/01/2011[/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]4[/TD]
[TD]Red[/TD]
[TD]04/01/2011[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Purple[/TD]
[TD]05/01/2011[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Purple[/TD]
[TD]05/01/2011[/TD]
[/TR]
</tbody>[/TABLE]
















[TABLE="class: grid, width: 500, align: left"]
<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]

 

[Fluff]

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))