Create new column based on duplicate row 1

[elsenorjose]

Sorry if the title wasn't very descriptive. Here is my question:

I have 2 columns in a worksheet:

code     bytes
18227	3592161
19750	246215
19750	225779
19751	83192
19751	65371
19752	20347
19752	20400
19753	55080
19753	54213

I need to display the data like this:

code     bytes1    bytes2
18227	3592161   0
19750	246215    225779
19751	83192     65371
19752	20347     20400
19753	55080     54213

How can I accomplish this?

Thank you.

 

[mintjulep]

Two-step process.

Assuming that your row header "code" is in cell A1.

Enter the formula

=IF(A3=A2,B3,0)

into cell C2 and copy down however many rows you have.

Enter the formula

=IF(C1=0,TRUE, FALSE)

into cell D2 and copy down.

Turn on auto-filter for columns A, B, C and D.

Filter column D to show only TRUE.

Hide column D.

Ok, maybe four steps.

 

[yogia]

hi elsenorjose:

If the original entries are in columns A and B, and the newly arranged entries are in columns D to F, then column D houses Unique entries from column A (using AdvancedFilter), and

the formula in cell

E2 is ... =INDEX($B$2:$B$10,MATCH($D2,$A$2:$A$10,0))
and
F2 is ... =IF(COUNTIF($A$2:$A$10,$D2)>1,INDEX($B$2:$B$10,MATCH($D2,$A$2:$A$10,0)+COLUMNS($A:A)),0)

these formulas are then copied down.

see following illustration

code	 bytes		  code	 bytes1	bytes2
18227	3592161		18227	3592161   0    
19750	246215		 19750	246215	225779
19750	225779		 19751	83192	 65371
19751	83192		  19752	20347	 20400
19751	65371		  19753	55080	 54213
19752	20347				
19752	20400				
19753	55080				
19753	54213

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[GlennUK]

Have a third column to distinguish byte1 items from byte2 items, like this:

="Byte "&COUNTIF($A$2:$A2,A2)

( please note the specifics of the $ signs in this formula ... the test range has a fixed start point and a relative end-point )

Have a title at the top of this column, say "Label" and create a pivottable to create your table, with "Code" as the Row field, "Label" as the column field, and "bytes" as the Data field ( Sum of bytes ). You can switch off Grand Totals for Rows in Table Options.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[elsenorjose]

Yogi's solution seemed the simplest and most efficient and worked perfectly, but thank you mintjulep and GlennUK for your solution, too.