Macro to move multiple columns into one

Status
Not open for further replies.

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
I posted this yesterday with no response; so I thought I'd try again and try and make my question a little more clear. Here are some examples of the data and then I'll ask my questions after the examples:

BEFORE
Book1
ABCD
1Before
2REPORTED_DEV_CLARIFICATION
31049 - Above;
41049 - Above;1069 - Shaft;1670 - Above RBP;
51049 - Above;1586 - Balloon;
61049 - Above;1586 - Balloon;1670 - Above RBP;
71049 - Above;1586 - Balloon;1670 - Above RBP;1586 - Tip;
81049 - Above;1586 - Shaft;1670 - Above RBP;1670 - Against Resistance;
91049 - Above;1670 - Above RBP;
101049 - Above;1670 - Above RBP;1528 - Balloon Catheter;1586 - Balloon;
111049 - Above;1670 - Above RBP;1586 - Balloon;
121049 - Above;1670 - Above RBP;1586 - Shaft;1586 - Balloon;
131049 - Below;
141049 - Below;1158 - Dislodged;
151049 - Below;1158 - Partial;1670 - Off Label Use;
161049 - Below;1528 - Guide Wire;
171049 - Below;1528 - Guide Wire;1670 - Off Label Use;
181049 - Below;1586 - Balloon;
191049 - Below;1586 - Shaft;
201049 - Below;1586 - Shaft;1670 - Off Label Use;
Sheet1



AFTER
Book1
ABCD
22After
23REPORTED_DEV_CLARIFICATION
241049 - Above
251049 - Above1069 - Shaft1670 - Above RBP
261049 - Above1586 - Balloon
271049 - Above1586 - Balloon1670 - Above RBP
281049 - Above1586 - Balloon1670 - Above RBP1586 - Tip
291049 - Above1586 - Shaft1670 - Above RBP1670 - Against Resistance
301049 - Above1670 - Above RBP
311049 - Above1670 - Above RBP1528 - Balloon Catheter1586 - Balloon
321049 - Above1670 - Above RBP1586 - Balloon
331049 - Above1670 - Above RBP1586 - Shaft1586 - Balloon
341049 - Below
351049 - Below1158 - Dislodged
361049 - Below1158 - Partial1670 - Off Label Use
371049 - Below1528 - Guide Wire
381049 - Below1528 - Guide Wire1670 - Off Label Use
391049 - Below1586 - Balloon
401049 - Below1586 - Shaft
411049 - Below1586 - Shaft1670 - Off Label Use
Sheet1


I have a query that brings back data that is separated by a semicolon. (Like the Before sample) I then break those apart into separate columns because each data after each semicolon is it's own code and I am using these in a listbox on a userform.

Manually this is no problem; but I need to validate where these codes are coming from, so I created a query that looks them up and refreshes the list accordingly. Again, this is all manually done right now.

I am looking to automate the steps after breaking the one column into multiple columns (that's easy) Is there a way to then grab all the columns and put them into one ongoing list in one column?
The problem I run in to is that the data could split into Columns B:F or sometimes it goes all the way to B:H so there doesn't seem to be an easy way that I know of to combine all these into one ongoing list.

Criteria that's a must.
1. I have to split them up (semicolon is the factor that helps with this)
2. I have to remove duplicates.
3. Defined Name Range will probably change every once in a while.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Status
Not open for further replies.

Forum statistics

Threads
1,136,354
Messages
5,675,299
Members
419,560
Latest member
g3org

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
Top