Transpose Varying Colums

[TravNoob]

Hi all, I'm a basic VBAer but this one has me stumped. I basically have a single column of data that I wish to convert to multiple columns.
Any assistance would be greatly appreciated.
Thank you.


From this (1 Column):

Race #13289347
Class*1
3500m
23/02/2022 10:32
3rd
26
Race #13288620
Class*1
3500m
23/02/2022 10:30
11th
Race #13287919
Class*1
3500m
23/02/2022 10:28
3rd
26
Race #13286369
Class*1
4000m
23/02/2022 10:23
6th
Race #13283093
Class*1
3500m
23/02/2022 10:10
11th
Race #13281097
Class*1
4000m
23/02/2022 10:03
6th
Race #13260386
Class*1
3500m
23/02/2022 8:32
2nd
44


To this (6 Columns):

Race #13289347 | Class*1 | 3500m | 23/02/2022 10:32 | 3rd | 26
Race #13260386 | Class*1 | 3500m | 23/02/2022 8:32 | 2nd | 44
Race #13281097 | Class*1 | 4000m | 23/02/2022 10:03 | 6th |
Race #13283093 | Class*1 | 3500m | 23/02/2022 10:10 | 11th |
Race #13286369 | Class*1 | 4000m | 23/02/2022 10:23 | 6th |
Race #13287919 | Class*1 | 3500m | 23/02/2022 10:28 | 3rd | 26
Race #13288620 | Class*1 | 3500m | 23/02/2022 10:30 | 11th |

[jindon]

Try

Please Login or Register  to view this content.

[TravNoob]

Amazing!! This worked perfectly! (sorry for sounding surprised :D).

Thank you some much!! Very, very much appreciated.

[jindon]

You are welcome.

[TravNoob]

I've realised this this actually swaps the Day and Month in the date. I've traced through the code and found that it transposes correctly but when it runs the TextToColumns it swaps the date and month. Any help is greatly appreciated. Thanks.


From:
Race #20863804
Class*1
3500m
10/03/2022 11:09
1st
105

To:
Race #20863804 Class*1 3500m 3/10/2022 11:09 1st 105

[jindon]

Try change bold line if this does not work as you expected.

Please Login or Register  to view this content.

[TravNoob]

Perfect! Thank you so so so much again.

[TravNoob]

While this works for double digit dates, it doesn't for single digits.

Eg.
From (is currently correct for me 7/03/2022 (7th March):
Race #19240886
Class*1
3500m
7/03/2022 9:00
1st
105

To:
Race #19240886 Class*1 3500m 3/07/2022 9:00 1st 105


Sorry to keep asking for help. I have seriously tried Google and trial and error to fix this myself but I just don't have the skills (yet!).

[jindon]

Race #13260386
Class*1
3500m
23/02/2022 8:32
2nd
44


To this (6 Columns):

Race #13288620 | Class*1 | 3500m | 23/02/2022 10:30 | 11th |

From (is currently correct for me 7/03/2022 (7th March):
Race #19240886
Class*1
3500m
7/03/2022 9:00
1st
105

To:
Race #19240886 Class*1 3500m 3/07/2022 9:00 1st 105

Confusing...

[TravNoob]

Are you saying that my threads are confusing? Sorry about that.

The first one in your reply was an example of what I want to happen and the second example is what your script does. Your script works fine for double digit dates say '23/02/2022' but not for single digit 7/03/2022 (7 March 2022).

See pic for an example of your script in action.

[jindon]

No, your output.

23/02/2022 10:32 to 23/02/2022 10:32 in post #1
7/03/2022 9:00 to 3/07/2022 9:00 in post #8

[TravNoob]

Apologies. I'm not explaining myself clear enough.

I've attached a sample spreadsheet with the script, if you run it you can see it transposes 10/03 perfectly which is what I'm after. However it changes 9/03 to 3/09 which is not what I want.


Thanks for bearing with me. Your help is so very appreciated.

[jindon]

You know the dates in Excel is terrible.

Here A4 shows as 2022/3/10 14:34:00 both in cell and formula bar.
That means it is genuine date and time.

How is it showing there?

[TravNoob]

Oh. Our setting differences would make this more difficult. Here is my screenshot.

[TravNoob]

And formula bar appears as "10/03/2022 2:34:00 PM".

[jindon]

OK.

Is the data imported from text file?
If so, it is better/safer to read the data directly from text file.

[TravNoob]

It's copied from a webpage and I paste it into excel with "Match Destination Formatting".

[jindon]

Can you copy it to text editor like NotPad?

Once you pasted to Excel, it automatically convert to appropriate data types, called auto casting, that I hate sometime...

[TravNoob]

Ok. So I did that and confirmed that all the cells in Column A are text. Still getting the same result unfortunately.

[jindon]

No,,,
What I'm trying to say is that once you pasted the data in text editor, read the data directly from the text file and process before output to Excel by vba.
So can you upload the text file here?

[Rick Rothstein]

Here is another macro (Sheet1 assumed for data and output) that you might find interesting (no loops)...

Please Login or Register  to view this content.

NOTE: I cannot test the output for the dates here in my locale so you will have to tell me if they outputted correctly for your locale.

[TravNoob]

Sorry, I'm unsure what you mean. I've just grabbed a section from the website and pasted it into notepad. Attached is the text file.

[Rick Rothstein]

We posted at nearly the same time so you might have missed my posting. See Message #21 -- right before your last post.

[TravNoob]

You guys are amazing! How do you do this in so little code! This works, just a minor formatting issue as the date/time transposes as text.

Still blown away at how skilled you guys are.

[Rick Rothstein]

This works, just a minor formatting issue as the date/time transposes as text.

Are you saying the date/time column is posting as text, not a real date/time? If so, is the day/month order correct? Let me know and I'll attempt to "fix" it (but first I need to know the actual problem).

[TravNoob]

The 'F' column is displayed as the 'raw' date/time "44630.61181".
Also it would be great to have the output sorted by Column C (smallest number on top).

The date/time is correct, just wrong format.

[jindon]

This will :

Show converted joined data in Col.A and separate it from col.C.

See if this works.

Please Login or Register  to view this content.

[Rick Rothstein]

I modified my original code a little as I realized I left some unqualified ranges. This code below fixes that, sorts your data as requested and attempts (because I cannot test your date ordering in my locale) to make the date/times real date/times instead of text... let me know if it worked or not.

Please Login or Register  to view this content.

[TravNoob]

I modified my original code a little as I realized I left some unqualified ranges. This code below fixes that, sorts your data as requested and attempts (because I cannot test your date ordering in my locale) to make the date/times real date/times instead of text... let me know if it worked or not.

Please Login or Register  to view this content.

Does the sort fine. Still doesn't format the date/time correctly. Data is correct, but format is not. Displays "44629.8458333333".

[TravNoob]

This will :

Show converted joined data in Col.A and separate it from col.C.

See if this works.

Please Login or Register  to view this content.

This incorrectly swaps the month and the date around for all entries, plus has the added step of opening the text file.
Really interesting way of doing things, haven't seen this before.

[jindon]

Then try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[TravNoob]

Then try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Swapped the "1" and the "0" and added the sort command from you previous script and it works great. Thanks so much for your effort. Still have that extra step of opening up the TXT file but it works. Thanks again!

[jindon]

OK, finally.
As I said

Is the data imported from text file?
If so, it is better/safer to read the data directly from text file.

[Rick Rothstein]

Okay, let me try one more time to get the date/time to display correctly (you still have to tell me if the day and month translate correctly though)...

Please Login or Register  to view this content.

[TravNoob]

Okay, let me try one more time to get the date/time to display correctly (you still have to tell me if the day and month translate correctly though)...

Please Login or Register  to view this content.

Works perfectly. Thank you!
Very fast and clean.