Excel - how to get similar individual rows to merge to one?

Question

0.00/5 (No votes)

See more:

Excel-2016

Hello!

This is sort of hard to explain but I basically want Orders summarized to one row then move the step column to their own cells following the unique order number to form a single row. Here is an example to visually show it:

Example Data:

Order     Amount   Step
ORD00001  100      Inspect
ORD00001  100      Measure
ORD00001  100      Ship
ORD00002  200      Cut
ORD00002  200      Inspect
ORD00002  200      Measure
ORD00002  200      Ship

What im looking to accomplish:

Order     Amount   
ORD00001  100     Inspect    Measure    Ship
ORD00002  200     Cut        Inspect    Measure    Ship

What I have tried:

I tried making a pivot table but its doesn't really work. The values show as a number rather then a string/text which I know can be converted in Conditional Formatting or some VBA, However this leaves a ton of blank columns since it will create a column for each step of which there are over 35 different steps and most orders only have about 6-12 and as the order goes through the process the data already eliminates completed steps.

I figure the only way is to probably do some VBA to copy the order/qty/step and move to another worksheet, if it finds a similar Order number, then paste the step to the next empty cell to the right. But wanted to see if any excel guru's knew of an out of box solution ( which I figure not but might as well ask before doing the VBA ).

Thank you for any input!

Posted 4-Jan-23 3:38am

Cody O'Meara

Updated 4-Jan-23 6:03am

v2

Add a Solution

Comments

0x01AA 4-Jan-23 10:22am

This looks not bad I think: How to quickly transpose group of rows to column in Excel?[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2023-01-04T06:03:00

Another alternative is to create a pivot table with "Step" in the Columns selection, "Order" in the Rows selection and "Count of Step" in the Values selection. On my sample the pivot table is in columns F to K, rows 1 to 5 and looks like this

Count of Step	Column Labels				
Row Labels	Cut	Inspect	Measure	Ship	Grand Total
ORD00001			1	1		1		3
ORD00002	 1		1	1		1		4
Grand Total	 1		2	2		2		7

I then used simple formulae to get the table in the format you describe - on the same sheet my results table is in Range M3:R4 with the following formulas

M3: =F2 						(i.e. the Order)
N3: =VLOOKUP(M3,A:B,2,FALSE)	(i.e. the Amount)
O3: =IF(G3>0,G$2,"")

i.e. if there is a count in the pivot table, display the header. I dragged O3 across to column R and down to row 4.

Results

ORD00001	100		Inspect	Measure	Ship
ORD00002	200	Cut	Inspect	Measure	Ship

EDIT:
My bad. I've re-read the question and spotted the "paste the step to the next empty cell to the right". Follow the same steps above, but amend to

O3: =IF(G3>0,G$2,"REMOVE!")

Then highlight your results, Ctrl-F (or Find & Select, Find).
In the dialog box
Find What: REMOVE!
Look In: Values <-- very important!

Click Find All, then Ctrl-A will select them all.
Close the Find Dialog box and hit Ctrl-MinusSign - Select "Shift Left" from the pop-up.

All your values will move left to fill in the gaps thus

ORD00001	100	Inspect	Measure	Ship	
ORD00002	200	Cut		Inspect	Measure	Ship

N.B. This doesn't work with the original formula and Find & Select, Goto Blank (which is the usual technique) because Excel does not recognise the "" as "blank"