VLOOUP and INDEX I think?!

[NJS1982]

Hi all, been a while! I'm creating another spreadsheet and wondered if anyone can help me out with it.

I have a survey that asks a question, with responses to the question being:
- Definitely
- Likely
- Not Sure
- Unlikely
- Definitely Not
- N/A

The downloaded sheet displays the answers in six columns; if the respondent selects 'Definitely' for example, 'Definitely' is populated in column A, with columns B, C, D E and F being blank. If they select 'Not Sure', columns A and B are blank, C contains 'Not Sure' and D, E and F are also blank. And so on....

Each line of the sheet is from one individual, whereby they are given a Unique Reference Number (a URN).

I am trying to pull together all responses, so on another tab (tab A) I have all of the URNs and am using VLOOKUP to extract individual responses.
I want tab A to have a column asking something like 'What was the response to question 10', for this cell to display which of the six responses listed above.

I believe I have to INDEX it somehow but can't work it out; can anyone please help?!?

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD]Respondent:[/TD]
[TD]Definitely[/TD]
[TD]Likely[/TD]
[TD]Not Sure[/TD]
[TD]Unlikely[/TD]
[TD]Definitely Not[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]URN 1[/TD]
[TD]Definitely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URN 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Sure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URN 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Unlikely[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URN 4[/TD]
[TD]Definitely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]URN 5[/TD]
[TD][/TD]
[TD]Likely[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SO, hopefully that makes sense. I am wanting a formula to tell me:

"What is the response to URN 1, for question 1"

 

[mole999]

assuming A1 is your start point, how about =TRIM(B2&C2&D2&E2&F2&G2) in H

 

[NJS1982]

Hi, thanks for the response. Hadn't thought of TRIM; seems to work if looking and working on one sheet, however...all of these responses are on another sheet, and I am trying to pull through via a VLOOKUP to another worksheet, like a summary.

So a lot of these kind of formulas, where '23' is the questions response:
=IF(A3="","",VLOOKUP(A3,'SMData'!$A$1:$CN$1000,23,0))

Is there a way to add TRIM into this formula, so column index numbered as 23 here is 'trimmed' to return just the right, single one (as in effect, the entire question covers six columns...)?

 

[mole999]

why not add the extra column on that sheet and do that work, then all you have to do is a index match between the results sheet and the data. just my first thought

 

[NJS1982]

Yes, would seem logical. But the process I have is to dump the downloaded data onto one sheet, for it to then simply populate onto another when the URN is added. If I add steps into the area where I dump all of the data, then it just complicates it for colleagues who don't have any kind of Excel skills, to be fair! I just want them to copy and paste the values of an entire (source date) sheet onto this workbook (the SMDAta tab), not add in certain columns on the SMData tab to work out additional formulas there.

 

[FormR]

"What is the response to URN 1, for question 1"

Hi, its not clear where the question number comes into play, but based on your sample table you could try something like:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Respondent:	Definitely	Likely	Not Sure	Unlikely	Definitely Not	N/A		URN 3	Unlikely
2	URN 1	Definitely
3	URN 2			Not Sure
4	URN 3				Unlikely
5	URN 4	Definitely
6	URN 5		Likely
Sheet30

Cell Formulas
Range	Formula
J1	=LOOKUP(REPT("z",255),INDEX(B:G,MATCH(I1,A:A,0),0))

 

[NJS1982]

Thanks; what is the "z" and the 255 in this formula represent?

My questions (so B1:G1) are actually:

Income generated - Definitely
Income generated - Likely
Income generated - Not sure
Income generated - Unlikely
Income generated - Definitely Not
Income generated - N/A

 

[FormR]

what is the "z" and the 255 in this formula represent?

It creates a large string to guarantee lookup value is larger than the values in lookup array, essentially forcing the lookup() function to return the last non-blank cell it finds.

 

[NJS1982]

It is kind of hard to explain it properly, so I have created a sample spreadsheet.
Does anyone know how I upload it to here for you to look at what I mean?

 

[NJS1982]

Tried Dropbox but don't think it works.
Excel online only shares to people in my work network.
Argh!