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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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:
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top