VBA - Delimited text to rows on a large dataset.

[pcsrvc]

Hi all!

This is my very first post here but I have done some research on the board and It is great how helpful you guys are! Thank you for that!

I'm using Office 365 on a Mac so it does not have the Power Query Editor, thus the idea of using VBA. I have never ever done anything with VBA before so I don't know even where to start with it.

I have a dataset like the below:
[Name] [Gender]
[ |ABELIO|ABILHIO|ABILIO|ABLIO|ADAELIO|ADAILIO|ADALIO|ADELIO|ADILHIO|ADILIO|ADLIO|ADUILIO| ] [ M ]

The dataset has 50743 rows and several of the cells have over 5 names delimited by |

It is my understanding that this dataset will yield approximately 130k names and as it is an old dataset there's only two genders, M and F.

I would like to use this dataset to perform a VLOOKUP from another sheet to validate if the name is likely to be a person's name or some other junk data.

Can anyone help me out with a VBA to transpose that data maintaining the Gender column information in each of the rows so that it looks something like
ABELIO M
ABILHIO M
ABLIO M
ADAELIO M
...

I truly appreciate the time you guys give here and hope to be helping people out in the future too as I'm only starting to study VBA for Excel.

Cheers!

 

[Jim Gordon]

Excel for Mac does have a PowerQuery editor. Perhaps you have not installed updates?

 

[pcsrvc]

Mine doesn't for some reason and the company disabled downloading add-ins, updating and such. Under "Data" I can't find the Power Query Editor.

I have however been able to modify a VBA code I found online and get the job done.

Thank you for helping!

 

[pcsrvc]

I got it now. I had to join the Office Insider Program by reinstalling the Office suite to include Microsoft Update Assistant, then I went to the advanced options in the Update Assistance and chose the Current Channel (preview) and that solved the issue.

Thanks again for your help!