Index&Match?

[littlejilly]

Hello am i trying to reformat some data that I have.

This is a data dump from a survey that I received from someone. the data has come in, all in columns. The users ID in column 1, the question numbers are in column 2, and the answers are in column 3.

Please note: not every respondent sees the same question numbers. Question numbers are randomly generated and therefore are not always in ascending order.

here is an example of the data I have

column 1: column 2: column 3:

100069 127 A
​

100069 128 B
100069 129 B
100069 131 D
100071 127 E
100071 128 F
100071 131 G
100071 135 H


I need my data to be formatted in a table that looks like this:

Q#s: 127 128 129 130 131 .... 135
user ID
100069 A B C D
100071 E F G H
next ID
next ID

where is question number is a column and each respondent spans a row

PLEASE HELP!

 

[baitmaster]

welcome to the board

you can answer this with an array formula. Its created like a normal formula, except you use shift + ctrl + enter instead of just enter, to submit it

Assuming your data is in A1:C7, create a list of the personnel numbers in column E, a list of answer numbers in Row 1, and type the following into F2:

=INDEX($C$1:$C$7,MATCH($E2&F$1,$A$1:$A$7&$B$1:$B$7,0))
then shift+ctrl+enter

 

[littlejilly]

Baitmaster,

Thanks you so much for your welcome message and for helping me out i truly appreciate it. I am afraid I am fairly new to excel and I am not sure what you mean a list of personnel numbers. i would like to create the re-formatted data in a sheet within the same workbook. do you think you could be more specific?

Thanks

 

[baitmaster]

sorry I mean User IDs

In my sheet, I have the following:
F1 = 127, G1 = 128 etc (i.e. question #s in that row of headers)
E2 = 100069, E3 = 100071 etc (i.e. User ID in that column)
F2:J3 my formula, created as per the description

Add more user IDs and question numbers as required, drag formula to extend your results

 

[MickG]

Try this:-
Results sheet(2)

[COLOR="Navy"]Sub[/COLOR] MG08Sep50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oHds [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR] .Item(Dn.Value) = Dn.Value
[COLOR="Navy"]Next[/COLOR]
oHds = .Items
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    ReDim Ray(1 To Rng.Count, 1 To Rng.Count)
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(oHds)
        [COLOR="Navy"]If[/COLOR] Dn(, 2) = oHds(n) [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
              p = p + 1
              .Add Dn.Value, p
              Ray(p, 1) = Dn.Value: Ray(p, n + 2) = Dn.Offset(, 2)
         [COLOR="Navy"]Else[/COLOR]
              Ray(.Item(Dn.Value), n + 2) = Dn.Offset(, 2)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
c = .Count
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    .Range("B1").Resize(, UBound(oHds) + 1) = oHds
    .Range("A2").Resize(c, UBound(oHds) + 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[littlejilly]

Baitmaster,

I continue to receive a #REF! error. I don't necessarily have to use Index&Match, this was just thought that is how it would be done, but as I stated earlier I am new to excel.

Thanks

 

[baitmaster]

INDEX(MATCH is the only way that I could get this to work myself, other than go down the coding route as Mick has

If you write it all exactly as described then I think it should work

PM me your email address and I'll send a file with this set up for you so you can take a look. I can't see why you're getting #REF! error, but it's a slightly more complex formula that needs to be configured exactly so who knows - probably easiest if I set it up and send it over