Not even sure how to explain this in a subject request

ayeready

New Member
Joined
Oct 26, 2018
Messages
18
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:

NumberColourDate
101/01/2011
101/01/2011
101/01/2011
202/01/2011
303/01/2011
404/01/2011
404/01/2011
505/01/2011
505/01/2011

<tbody>
</tbody>


Second sheet looks like this:

NumberColourDate
1Black
2White
3Blue
4Red
5Purple

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

NumberColourDate
1Black01/01/2011
2White02/01/2011
3Blue03/01/2011
4Red04/01/2011
5Purple05/01/2011

<tbody>
</tbody>

Is this possible?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Number</td><td style=";">Colour</td><td style=";">Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">Black</td><td style="text-align: right;;">01/01/2011</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2</td><td style=";">White</td><td style="text-align: right;;">02/01/2011</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">3</td><td style=";">Blue</td><td style="text-align: right;;">03/01/2011</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">4</td><td style=";">Red</td><td style="text-align: right;;">04/01/2011</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">5</td><td style=";">Purple</td><td style="text-align: right;;">05/01/2011</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet1!$C$2:$C$10,MATCH(<font color="Red">A2,Sheet1!$A$2:$A$10,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

ayeready

New Member
Joined
Oct 26, 2018
Messages
18
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
In what way doesn't it work? It looks to give the same output as you showed.
 

ayeready

New Member
Joined
Oct 26, 2018
Messages
18
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
A formula cannot "Insert" rows.
Are you simply trying to get the colours from sheet 2 onto sheet1?
 

ayeready

New Member
Joined
Oct 26, 2018
Messages
18
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
Can you please explain what you need as an end result, forget any intermediate steps.
 

ayeready

New Member
Joined
Oct 26, 2018
Messages
18
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.

NumberColourDate
1Black01/01/2011
1Black01/01/2011
1Black01/01/2011
2White02/01/2011
3Blue03/01/2011
4Red04/01/2011
4Red04/01/2011
5Purple05/01/2011
5Purple05/01/2011

<tbody>
</tbody>
















NumberColourDate
1Black01/01/2011
2White02/01/2011
3Blue03/01/2011
4Red04/01/2011
5Purple05/01/2011

<tbody>
</tbody>
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,075
Office Version
365
Platform
Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,090,367
Messages
5,414,030
Members
403,512
Latest member
mrmdsims

This Week's Hot Topics

Top