Rearrange Cells to Match Other Cells
(Excel Doc Attached)
I am trying to rearrange the cells in column B to match the cells in column A, by searching each cell in column A partially based on the cells in column B.
For Example:
A1 = 123ABC
B6 = ABC
B6 is ABC and A1 has ABC in it's contents.
What I want to happen, is for the information in B6 to move to B1 because A1 contains some of B6's contents.
Hi Patric,
This formula works for your example but may not for a larger sample of data:
Formula:
Please Login or Register to view this content.
See it in the attached:
Index Match Right or Mid.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Sorry that it took me a while to get back with you. It works for the most part, but not with a vast amount of data (Like you thought might happen). See, the sheet that I'm using this formula/code for, has over 40,000 rows of data. When I use the formula you gave me on that many rows, it nearly crashes my computer. To combat this, I made sure to end all other processes on my computer and then try again with a smaller amount of data. Still nearly crashed my computer. So yeah, still in need of help here, thanks though.
This might be an instance where adding a helper column reduces the overall computational load.
In the file attached to post #1:
Put the following into cell F3 and copy down to cell F5: =SUMPRODUCT(ISNUMBER(SEARCH(B3,A$3:A$5))*(ROW(A$3:A$5)))-2
Put the following into cell E3 and copy down to cell E5: =INDEX(B$3:B$5,MATCH(ROWS(A$1:A1),F$3:F$5,0))
Note that column F could be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
This may work better for column F:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks