hi all
back again lol
basically please see excel spreadsheet below. i know it would be easier if all was in the same column, however thats not possible as this is only one question from 60 imported from a online survey. i cannot control how the date is exported from survey monkey.
what i need to happen is for column H to search across columns B-G and then convert the text in to the number as per the values bottom right
is this possible
mick
Hi,
H2: copied down
Formula:
=VLOOKUP(B2&C2&D2&E2&F2&G2,$K$22:$L$27,2,FALSE)
You'll need to take the trailing space from the word never in K22.
In addition name your lookup table and use the name in the formula instead.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
richard
thank you
when i name the table values does this mean instead of $K$22:$L$27 it will read values do i need to place any $ signs on there?
Hi,
See attached
richard
cancel my friend i have used what you have done and worked it our from there thank you again that was amazing
Seems there is only 1 response per line/person so do you want the value in Total to represent that value or do you want the actual response to be modified to a number?
You could do a Search and replace on the 7 possible answers or we could use a VLOOKUP in the Total Column.
NOTE: Your "never" in your list contains an extra space ("never ") and will not work in the vlookup. Remove the space from cell K22
Last edited by ChemistB; 02-22-2013 at 09:48 AM.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
chemistb
sorry just noticed your post, Richard has sorted it for me thank you so much for your input
mick
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks