Data Find and Sort

Justijb

New Member
Joined
Aug 16, 2016
Messages
43
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!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,047
Office Version
365, 2010
Without trying to reconstruct your data in all of those columns, I suggest you try a

Code:
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)
 
Last edited:

Justijb

New Member
Joined
Aug 16, 2016
Messages
43
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

New Member
Joined
Aug 16, 2016
Messages
43
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

New Member
Joined
Aug 16, 2016
Messages
43
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,995
Messages
5,471,888
Members
406,790
Latest member
Crones8

This Week's Hot Topics

Top