VBA help with IF statement and moving to a new column

[good ol gal]

I new to VBA and would like help trying to figure out how to write this code.

I have a file that has multiple columns that may have the same data..but not in the same column and I want to have all the same data in the same column.

For example: Column D has last names and Column E has last names. I want to put all of the last names into a column.

I tried doing an If Then statement, but it keeps coming back as an error.

Columns("F:F").Select
If LRegion = "Building IRN" Then
Columns("E:E").Cut Destination:=Columns("Y:Y")
ElseIf LRegion = "Employee ID" Then
Columns("E:E").Cut Destination:=Columns("Z:Z")

I was thinking that I would just do a cut/paste to the very end of the spreadsheet, then after all the of the code is done, I will enter a code in to delete all the empty columns that will now be at the beginning of the worksheet after they've all been moved.

I am attaching a copy of a sample worksheet for anyone to look at.

Thanks so much for your help! I am having a ball learning this!! VBA If Then.xlsm

[FDibbins]

Hi and welcome to the forum

Seeing as this is a VBA question, I will move it to that forum for you

[good ol gal]

Thanks for moving it to the correct forum.

[Tsjallie]

Just checking if I get things right.
As I look at your sample it looks like each cell containing data is followed by a cell specifying the type of that data.
For example:

100000001 Employee ID Last Name 88 Last Name First Name 55 First Name WW567845 State Staff ID

So "100000001" is an Employee ID, "Last Name 88" is a Last Name, "WW567845" is a State Staff ID, etc.
If that is correct then it shouldn't be to difficult to sort this out.
To give you an idea I'm uploading your workbook with a sample. Run the GroupData procedure in Module1.
If this way beyond what you want, would it be possible for you to take a few samples and post how the result should look like for you?

[good ol gal]

Yes, I will do that this afternoon. Thank you!

[good ol gal]

Here is the file. "Complete" is what I would like to see once the macro has completed. "Data" is the raw data that I will be working with.

VBA If Then complete.xlsm

I have been playing around myself trying to get it to work still with no success. I was thinking if it could move the data to a new worksheet that would be fine too.

And yes, all of the columns will have the column title to the right of them.

[good ol gal]

I tried running the Group Data macro, but it didn't do anything. What is on the formatted sheet is what I would like.

[Tsjallie]

Strange that it "didn't do anything". Can't reproduce that.
Anyway, here's your workbook with the GroupData() procedure in it. Put some comment in it to explain.
Should produce the Complete sheet as you specified. Have run it over-and-over with no errors and the expected result each time.
I did however notice that you somehow sorted the Complete sheet, but can't figure out the criteria you're using.

[good ol gal]

That file is AMAZING!!!!!

It did exactly what I was hoping to accomplish. Of course I'm going to have a follow-up question.... Do I have to have the "complete" tab already created with the column titles on it for it to work? I think it does. Once I download the file, it only has the data sheet on it(and it is not called "data"). I can just write a macro to create the new worksheet and label the columns the right way...right??

[Tsjallie]

I can just write a macro to create the new worksheet and label the columns the right way...right??

Indeed you can. Also the names of the columns do not matter, because the macro uses column numbers.
But you do have to make some changes to the macro to use a variable name for the "Complete" worksheet and - if not fixed - for the "Data" worksheet.
You only have to make these changes in the top section of the macro where the ranges are defined.
You could also decide to have both raw data and grouped data on the same sheet though I would keep 'm apart.
Also notice that the order of the columns and their position (1-7) is fixed in the macro. So if you change that you also would have to edit the macro for that.
Let me know if I can help you any further.
And - if your problem is solved - please mark this thead "Solved".

[good ol gal]

Thank you Tsjallie. I have figured out how to add a new sheet called Complete label my columns. I truly appreciate your help. I pull a file like this everyday with about 30 different column fields. Now I can just follow your pattern and continue on and make a process that would literally take me hours to complete into seconds!

Amazing program and Amazing people here willing to help!

[good ol gal]

Well, now I'm encountering a problem because when I text to column it based upon my delimiters, there is one column that does not have the parameter after it.

For example:

A = Smith
B = John
C = Employee Name

The case "Employee Name"
CompleteRange.Cells(ri, 5) = DataRange.Cells(ri, ci -1) will move John to the correct column, but I can't get Smith to move.

I tried changing the - 1 to - 2, thinking it was how many columns before the Case "Employee Name", but that did not work.

Do you have any suggestions? I have gotten so many of my other codes working.
I rename my sheet, create my new worksheet, move columns A:D over to new sheet, find and replace, text to column, freeze panes, auto filter, delete the old "data" sheet, and have added more case criteria for up to AR columns! With your help I am so excited!

Do you have any suggestions on how to get the Employee Last name moved along with the First Name? I just want to move it to a new column, they don't need to be concatenated.

Thanks!

Indeed you can. Also the names of the columns do not matter, because the macro uses column numbers.
But you do have to make some changes to the macro to use a variable name for the "Complete" worksheet and - if not fixed - for the "Data" worksheet.
You only have to make these changes in the top section of the macro where the ranges are defined.
You could also decide to have both raw data and grouped data on the same sheet though I would keep 'm apart.
Also notice that the order of the columns and their position (1-7) is fixed in the macro. So if you change that you also would have to edit the macro for that.
Let me know if I can help you any further.
And - if your problem is solved - please mark this thead "Solved".

[Tsjallie]

Is it possible for you to upload your workbook so I can have a look at it?

[good ol gal]

Here is a sample.

Is it possible for you to upload your workbook so I can have a look at it?

[Tsjallie]

Are you sure you uploaded the right sample. Looks rather different and doesn't show the situation like you described in post #12
What I want to find out is what rule to setup to also do a correct transformation in the situation you're encountering now.
We started out with a simple rule: "data is always in the cell left of the cell with the column name" and implemented that in the select case construct, but apparently there are more rules needed.

[good ol gal]

It is the same file, I just deleted most of the lines. I will upload the whole file again tonight.

[good ol gal]

Are you sure you uploaded the right sample. Looks rather different and doesn't show the situation like you described in post #12
What I want to find out is what rule to setup to also do a correct transformation in the situation you're encountering now.
We started out with a simple rule: "data is always in the cell left of the cell with the column name" and implemented that in the select case construct, but apparently there are more rules needed.

Here is a better copy of the file. I added in columns A-D which I have copied over to the new worksheet.

I already ran my text to columns and find and replace on it.

Thanks again for your help.

[Tsjallie]

Here are the changes I would suggest.
The situation is that (apparently) there are more than 1 recordtype in your datafile.
To handle that we need to expand the procedure to also handle different recordtypes.

Basically the procedure should be as follows:

Please Login or Register  to view this content.

I'm posting a portion of the new code.

As far as I can see recordtype 2 can be recognized by the presence of a cell containing the string "Employee ID", which seems to be absent in the case of recordtype 1.
The code marked red is handling this.
For the identification the match-function is used. However to have function give reliable results any leading and trailing spaces need to be eliminated.
The code marked green is handling this.

Please Login or Register  to view this content.

Hope this get's you back track.

Finally, there's one thing in your code that puzzles me: it looks like - at the end - you're throwing away the result of the procedure.

Please Login or Register  to view this content.

[good ol gal]

I will check out all your new additions this evening!

The code below deletes the page that contained the original data. I only want the new worksheet that has all of the formatted data left when I'm all done.

Sheets("data").Select
ActiveWindow.SelectedSheets.Delete