rearrange data into a certain format

Greetings all,

I am seeking the expertise of you knowledge folks to advise me whether it is possible to create a "script" in Excel to jumble up certain letters in an entry?

Let me explain. What I have are 4 separate columns with data in them. I need to somehow rearrange them into a certain format, like an encryption I suppose.

Here's an example of some data I have :

COL 1 COL 2 COL 3 COL 4
ABCD EFGHIJ KLM NOPQ

What I need to do is jumble those up so they appear in the following format:

COL 1 COL 2 COL 3 COL 4
CDAB GFEHIJ LKM NPOQ

Is it possible to do this in Excel? You're probably wondering why I don't do it manually, but there are over 20,000 records that need jumbling up and obviously that would take a very long time.

Can anyone advise please?

Thanks a lot for reading.

Kind regards,

Snooze

Good evening chaps,

Thanks for your replies. Jindon, I'm afraid my knowledge of Excel is fairly basic as the vast majority of my work is with Access. Could I seek some further help from you please? What should I do with your code now?

Would I be right in thinking that I open my Excel database, somehow run your code over it and then a few moments later the database will pop out the other end all encrypted?

The end result actually needs to be a .dat file and in the following format (based on my example in my original post), with each entry on a new line:

CDAB=GFEHIJ,LKM,NPOQ

Column 1 is always 4 letters
Column 2 is between 2 and 10 characaters - a mix of letters, numbers and hyphens (the encryption only shows the first 3 characters backwards; all the others are in order)
Column 3 is always 3 letters
Column 4 is between 3 and 4 characters - a mix of letters and numbers

Would saving the file as .csv and then as .dat achieve the required format or would it be more complicated than that?

Thank you once again.

Kind regards,

Snooze

So once it gets scrambled, how does it get unscrambled?

Originally Posted by shg

So once it gets scrambled, how does it get unscrambled?

By another program.

Jindon, did you receive my reply please?

Regards,

Snooze

Thanks for your replies. Jindon, I'm afraid my knowledge of Excel is fairly basic as the vast majority of my work is with Access. Could I seek some further help from you please? What should I do with your code now?

1) Hit Alt + F11 to open vb editor
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 to get back to Excel
4) hit Alt + F8, select "test", then hit [Run]

Originally Posted by jindon

1) Hit Alt + F11 to open vb editor
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 to get back to Excel
4) hit Alt + F8, select "test", then hit [Run]

H Jindon,

Sorry for my late reply. Thanks for your help once again.

I have two issues with it though. The first one threw up an error message

Compire error!

Sub or Function not defined!

I found out what that was - it was the word Randmize in your code, which I changed to Randomize and it seems to be happy with that, but now when I try it I get another error :

Can't execute code in break mode

and it's highlighting the first line in yellow :

Sub test ()




I look forward to your reply and thanks once again.

Regards,

Snooze

I also have 2 additional issues which I would like to seek your help with if I may.

I need to save the data in the following format:

CDAB=GFEHIJ,LKM,NPOQ

Saving as .csv nearly does it, but there needs to be an = between column 1 and column 2. Is this possible or will I have to combine the data in both columns into one, eg. column 1 = CDAB=GFEHIJ ? I can easily do this, but I guess that this will affect how your code is set-up Jindon?

Secondly, on a numeric entry of say 00123, it keeps deleting the leading zeros. I have changed the entire sheet format to the 'text' option and saved it accordingly (as .csv), but when I reopen it again, the leading zeros aren't there and the cell is being treated as a number format. How can I stop this happening please?

Thank you.

Regards,

Snooze

Originally Posted by Snooze

H Jindon,

Sorry for my late reply. Thanks for your help once again.

I have two issues with it though. The first one threw up an error message

Compire error!

Sub or Function not defined!

I found out what that was - it was the word Randmize in your code, which I changed to Randomize and it seems to be happy with that, but now when I try it I get another error :

Thanks!

Can't execute code in break mode

You probably hit "No", when it debugged.
Go To the top menu in VBE and hit squared icon (Stop), then run again.
Where you can see triangle to "Play", square to "Stop"

Sorry, I need to go off-line now and will be busy tomorrow.
I'll post the rest.

Please show me, what you have and what you want in data format...