Converting strings to unique number pattern

Can you attach a sample of your source data and what you expect your output to look like?

Hi kerplash,

i would like to attach a sample but this is more theoretical question than sample, i do not have sample.

How to show unique string or number if it can be a concatenation of special signs or digits...

Best,
Jacek

In theory I'd love to help you too.

Workbook added,

Best,
Jacek

What i am thinking maybe macro can check the longest string in a row and based on that set up number of special signs or digits (and remember to not exceed 255 characters).
So when i will have 18 columns the longest string will have 15 signs and i can check that unique number for each string can be 237/18 (255 minus "-" x 18 for concatenated field) = 14, so i should create unique fields max length = 10 for example.

Best,
Jacek

Hi mikerickson,

thank you for getting involved,
I added 18 columns and create Key field where concatenated string exceed 255 characters.
If i would change each same string for unique characteristic signs or digits, i could not exceed 255 number.

Best,
Jacek

Hi Guys,

what do you think about hashcodes, they can be useful in this case?

Best,
Jacek

Hello,

anyone can help?

Best wishes,
thank you in advance,
Jacek Antek

Hi jaryszek,

i love difficult topics so i did research and find great solution for you, try to use hash codes,

try this code to convert string to hashcode:

Please Login or Register  to view this content.

So you can convert your each field in every row to hash code (using loop and arrays for examples), and after that joined all fields together in order to get key.
Of course if you will use too small amount of signs there is a probability to get "collision" but using SHA1 + Base64 hash code (my favorite one) and keeping about 10 signs for field you are almost sure that your data will work without any issues and collision thing. Tested on 10 thousends rows.

I do not think that there is other solution, maybe other people can help, but try mine ;-)

Paul Ant

Hi Paul,

thank you very much, wow this is i thing great solution.

Thank you,
i am not closing this topic because maybe other Excel Masters know better ways.

Best,
Jacek

hi Paul,

i tested code and seems to work nice - maybe this is not speed demon but working.
I do not think there is a possibility to do this better, you are awesome man!

Thank you once again,
Best,
Jacek