Data Find and Sort

[Justijb]

Good Afternoon,

Absolutely impressed with the feedback on this forum - hoping I have the same luck again. I have a bit of an issue. I have a worksheet with columns A through AG. All of the data is similar but the column that needs the script to be focused on is column AE.

Column AE has random text, codes, and words. What I need is a script to match specific words, codes, and texts to a table that acts like a legend. E.g., column AE has DOG / seperate table has DOG and in that table DOG = DOG20; Column AE has CAT / and in table CAT = DAR9

the output would be column AE: random words and texts (dog, cat) to column AH = DOG 20, AI = DAR9 etc.

I would just need the formula so i could update the specific strings of text to transpose them to same row but different columns for reference.

Thanks!

 

[kweaver]

Without trying to reconstruct your data in all of those columns, I suggest you try a

VLOOKUP(AE1,MyLegend!$A$1:$B$100,2,false)

and modify the MyLegend sheet rows to whatever you need (e.g., $A$1:$B$1000)

 

[Justijb]

Thanks for the reply kweaver. Much appreciate it. Since the rows of data is over 100k, i would rather try a macro that seperates the key data (from the legend i created) to columns so I can sort easily. Essentially trying to look in a text box for key words from the legend and then transpose those key words into columns for over 100k rows.

In the comment box, theres cat, dog, mouse, not in order and could be blank then turn it into column comment box, then column cat, column dog, column mouse.

 

[Justijb]

Good Morning All,

I wanted to see if there was any more movement on this thread. Any assistance would be very much aprpecaited. As I said trying to convert text/ code in one column into mulitple columns to match a criteria I developed.

Thank you!

 

[Justijb]

I wanted to bump this thread because I always get great feedback. Hopefully it's an easy VBA script to write but wanting to sort on a column with texst and number data and compare that a table of keywords to match to create seperate worksheets on those matches with associated row data. Apologies for continuing to ask but i feel like the solution is there. im just not seeing it. Thanks so much.