Rearranging Columns Based on Values in Row 1

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
214
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a worksheet in which there are numerous columns that are indexed by a value in Row 1. For example, it might say 1 Odd, 1 Even, 2 Odd, 2 Even, and so on. Of course they aren't in any kind of order, and I would like to use a macro to rearrange the columns in a particular manner. I want to arrange them so they alternate by Evens and Odds per digit, so for example 1 Even, then 1 Odd, for however many of 1 Even and 1 Odd columns there are, then alternating with 2 Even and 2 Odd, and so on. Also, if there is only one kind of a category, then a blank column should be inserted to separate them. Here is an example of the original data:

Waves2 Odd1 Even2 Odd2 Even1 Even2 Odd2 Even3 Odd3 Even
HOME DOOR
Shipment ID222181122217852221781222177922217942221784222177422217752221782
Carrier CodeWVAS.WVAS.WVAS.WVAS.WVAS.
3STOP
Store64613117772267633237511251579646
CP/XD IndicatorXDXDXDXDXDXDXD
Weight88913230833789320842885929530265102492838180
Cube76121802376215722292311150016602154
Lines1211631098137014297493437631485
Cut TimeTU9AMTU8PMTU9AMTU9AMTU10PMTU9PMTU10PMTU11PMTU10AM

And here is how it should look after rearranging the columns:

Waves1 Even1 Even2 Even2 Odd2 Even2 Odd2 Odd3 Even3 Odd
HOME DOOR
Shipment ID222178522217942221779222181122217742221781222178422217822221775
Carrier CodeWVAS.WVAS.WVAS.WVAS.WVAS.
3STOP
Store13163322676461125177723756461579
CP/XD IndicatorXDXDXDXDXDXDXD
Weight32308288593208488912651033789295303818024928
Cube21802229215776115002376231121541660
Lines1163142913701234310987491485763
Cut TimeTU8PMTU10PMTU9AMTU9AMTU10PMTU9AMTU9PMTU10AMTU11PM

As you can see, there were two 1 Evens with no 1 Odds, so the two were separated by a blank column, then it went to 2 Even, 2 Odd, 2 Even, followed by two 2 Odds in a row, then 3 Even and 3 Odd.

Here is how they actually look for real...Before:

1623015575038.png


After:

1623015617927.png


Does anyone have any suggestions on how to code this?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,215,029
Messages
6,122,755
Members
449,094
Latest member
dsharae57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top