Need to convert the items to 1 item per line with the data in column B to the same row

**Dolsmi** · 12-28-2015, 09:37 AM

Need assistance on how to convert a spreadsheet.
I need to take the data in column A, multiple identical items with different data in column B, and convert to 1 line with the data from column B converted to that same line an put the column B data in the same row in consecutive columns.

So for example on the first item I would like to convert looks like this:

010220 GS05E4
010220 GS09H1
010220 PRU1A
010350 GS05E2
010350 PRU1A
010350 TR06A3

But I want to convert it so all the same item is on 1 line with the data in column B extended out in the same line like this:
010220 GS05E4 GS09H1 PRU1A
010350 GS05E2 PRU1A TR06A3

Any assistance is greatly appreciated
Thanks!!

**FlameRetired** · 12-28-2015, 09:59 AM

In column D I copy / pasted the column A values and applied Remove duplicates.

Then in E1 as below.

Row\Col	A	B	C	D	E	F	G	H
1	010220	GS05E4		010220	GS05E4	GS09H1	PRU1A	In E1 filled down and across
2	010220	GS09H1		010350	GS05E2	PRU1A	TR06A3	=INDEX($B$1:$B$6,MATCH($D1,$A$1:$A$6,0)+COLUMNS($A:A)-1)
3	010220	PRU1A
4	010350	GS05E2
5	010350	PRU1A
6	010350	TR06A3

**alansidman** · 12-28-2015, 10:01 AM

Here is a VBA solution

Please Login or Register  to view this content.

How to install your new code

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

**Dolsmi** · 12-28-2015, 10:43 AM

Awesome!!! That is exactly what I needed.
Thank you very much!!!!

**FlameRetired** · 12-28-2015, 11:02 AM

To which solution do you refer?

**Dolsmi** · 12-28-2015, 11:07 AM

I tried both options that you and Alan supplied.
They both worked great and I got the result I was looking for from each one. By trying both options it gave me addition excel knowledge!
Thanks again!!

Originally Posted by FlameRetired

To which solution do you refer?

**FlameRetired** · 12-28-2015, 12:04 PM

Good news, and thanks for the feedback.

**alansidman** · 12-28-2015, 12:39 PM

Thank you also for the feedback. Please mark your thread as solved. Go to thread tools at the top of the post

**newdoverman** · 12-28-2015, 12:50 PM

If this is a growing list of unknown limit, these formulae (expanded from FlameRetired) will accommodate this.
First to get the list of values from column A without duplicates:
Enter in D2 and fill down

Formula:

Please Login or Register  to view this content.

Enter in E2 and fill across and down. This will cut off the columns when the data runs out for the value in column A.

Formula:

Please Login or Register  to view this content.

The second formula is derived from FlameRetired's formula

	A	B	C	D	E	F	G
1	10220	GS05E4
2	10220	GS09H1		10220	GS05E4	GS09H1	PRU1A
3	10220	PRU1A		10350	GS05E2	PRU1A	TR06A3
4	10350	GS05E2		1111	xyz123
5	10350	PRU1A		12345	YYY789	UYO897
6	10350	TR06A3
7	1111	xyz123
8	12345	YYY789
9	12345	UYO897

**Dolsmi** · 12-28-2015, 01:38 PM

Thank You!!
Very helpful!

Originally Posted by newdoverman

If this is a growing list of unknown limit, these formulae (expanded from FlameRetired) will accommodate this.
First to get the list of values from column A without duplicates:
Enter in D2 and fill down

Formula:

Please Login or Register  to view this content.

Enter in E2 and fill across and down. This will cut off the columns when the data runs out for the value in column A.

Formula:

Please Login or Register  to view this content.

The second formula is derived from FlameRetired's formula

	A	B	C	D	E	F	G
1	10220	GS05E4
2	10220	GS09H1		10220	GS05E4	GS09H1	PRU1A
3	10220	PRU1A		10350	GS05E2	PRU1A	TR06A3
4	10350	GS05E2		1111	xyz123
5	10350	PRU1A		12345	YYY789	UYO897
6	10350	TR06A3
7	1111	xyz123
8	12345	YYY789
9	12345	UYO897

Need to convert the items to 1 item per line with the data in column B to the same row

LinkBack

Thread Tools

Rate This Thread

Display

Need to convert the items to 1 item per line with the data in column B to the same row

Re: Need to convert the items to 1 item per line with the data in column B to the same row

Re: Need to convert the items to 1 item per line with the data in column B to the same row

Re: Need to convert the items to 1 item per line with the data in column B to the same row

Re: Need to convert the items to 1 item per line with the data in column B to the same row

Re: Need to convert the items to 1 item per line with the data in column B to the same row

Re: Need to convert the items to 1 item per line with the data in column B to the same row

Re: Need to convert the items to 1 item per line with the data in column B to the same row

Re: Need to convert the items to 1 item per line with the data in column B to the same row

Re: Need to convert the items to 1 item per line with the data in column B to the same row

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculate the total of a unique item and place in column G and make remaining items 0

[SOLVED] Calculate the total of a unique item and place in column G and make remaining items 0

Macro to convert date range to individual line items (i.e. rows) in excel

selected item from column to line

Cross Reference item from one colum to items in a new column

Combining multiple line items into one line item based on column.

Convert Horizontal Line Items to Vertical?

Bookmarks

Bookmarks

Posting Permissions