Click here to Skip to main content
15,919,613 members
Please Sign up or sign in to vote.
0.00/5 (No votes)

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!
Updated 4-Jan-23 6:03am
0x01AA 4-Jan-23 10:22am    

1 solution

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.

ORD00001	100		Inspect	Measure	Ship
ORD00002	200	Cut	Inspect	Measure	Ship

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"
Share this answer
Cody O'Meara 4-Jan-23 14:35pm    
This works perfectly, Thank you very much!
CHill60 5-Jan-23 4:44am    
My pleasure!
0x01AA 4-Jan-23 14:48pm    
Wow! 5
CHill60 5-Jan-23 4:44am    
Thank you!

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900