Log In
Register
Home
December 27, 2017
Offline
I have a spreadsheet where I want to look for a word in a row and then put that word in a different column.
I've attached a sample of my spreadsheet - it's not a full view but will give you an idea of what I'm looking for.
I want to search each row in column DA. I will be searching for the words in column CY. When I find the word in column DA that matches a word in CY I want that word to be placed into column CZ. I hope all of that makes sense.
See Answer
VIP
April 21, 2015
Offline
Welcome on Myndas Forum Troy. You have four rows with words in column CY. Do you want to lookup on all those four? And where do you want to place the ones who match? Let's say in DA2 two of the five match. Do you want those two words in CZ2?
Not sure what you want (and if that's possible), so I hope you can explain.
Or is it just plain matching CY2 with DA2 and CY3 with DA3 and so on? (that'll make it a lot easier I think)
Frans
Answers Post
December 27, 2017
Offline
Frans -
I'd like to search each row in column DA. The words I'm searching for are in CY. Each row in DA will match only one of the CY words. Once the match is found I want it listed in column CZ.
example - search D2 for words in CY - once the word is found enter it into CZ 2 and so on down the line for all rows in DA.
hope that helps.
here is some info I found on this but I'm a newbie and don't quite understand although it looks like it will work.
VIP
April 21, 2015
Offline
OK Troy, thanks for the hint to the explanation on this website!
I converted it to your case and with this example, I hope you'll find your way.
Honestly: I wouldn't found this solution myself 🙂
Frans
PS: I choose the non array formula, because I'm not so familiar with the array formulas and this one worked good I think
December 27, 2017
Offline
that seems to work.
When I apply that to my full Excel spreadsheet it don't get the same results. I can't send the full sheet as it has too much personal info. I've attached the excel spreadsheet you've sent back. Plus a larger sample of my actual spreadsheet.
the only difference on my real estate sample attachment is that the columns will change but not the rows. will that affect your formula?
December 27, 2017
Offline
I also noticed that if I just copy and paste the exact words you had in column D (for your example) under features it seems to work. But you will notice on my Real Estate Samples spreadsheet that column D is not very uniform. Does that change things?
VIP
Trusted Members
June 25, 2016
Offline
Hi Troy
Give this a try.
I think the problem is because multiple words are found in the cells.
E.g. Cash and Conventional both exist in cell C2.
To work around this problem, I searched for the word Loan Type|Cash (for example), which is unique.
I then replaced the word Loan Type| to show only the word Cash
Note : This is an ARRAY formula so you need to use CTRL+SHIFT+ENTER instead of ENTER
Hope this helps.
Sunny
December 27, 2017
Offline
this works great - I only have one more thing.
when I copy the formula to my master spreadsheet I don't get the same results. Oddly enough when I copy my master spreadsheet to the spreadsheet with the correct formula then it works fine. you will see a portion of my master spreadsheet on sheet 2 of the attachment. I've left out the personal data.
how can I copy this formula to my master spreadsheet and get it to work? I will need to use the formula at least once a month when we download new data so i'll need to be able to copy it over and over.
Thanks for all of your help on this - it's been great.
December 27, 2017
Offline
by the way - when I copy my complete master spreadsheet to the spreadsheet you provided with the correct formula it works great. I just copied it to a new sheet with the same workbook and the formula you provided carries over.
VIP
April 21, 2015
Offline
Hi Troy and Sunny, good to see Sunny provided a working solution for your case!
You wrote: "how can I copy this formula to my master spreadsheet and get it to work?"
Did you try the way Sunny described? Just copy the formula without the special brackets and then give the CTRL+SHIFT+ENTER. That'll make those special brackets for you (and otherwise it won't work).
Frans
December 27, 2017
Offline
I've attached a slimmed down master excel spreadsheet so you can see the formula I've used. To me it looks identical to the one that was provided. not real sure what i'm doing wrong.
the new master spreadsheet is attachment Troy Sample 2
the excel spreadsheet with the correct formula is Troy Bradley Real Estate Sample
VIP
April 21, 2015
Offline
Hi Troy,
In the sheet where the error occurs, your reference to 'list' is to the A column and not to the proper CS column. I think when you fix that, you have your working sheet.
Frans
1 Guest(s)
