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?

 

[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.

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

<tbody>
</tbody>

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>

 

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