Alternative to the V-LOOKUP? | Fairly simple table problem (PART 3)

[James12513]

Hello everyone,

I am trying to find a formula that will transform the data in the "data table" into a format like the "presentation table" below.

I had a similar problem and some great guys gave some brilliant formulas that really helped. I am wondering if anyone knows a way that this can be done?

Similar table problem: http://www.mrexcel.com/forum/excel-...imple-table-problem-part-2-a.html#post3885424

Thanks ever so much,

James


Data Table

ID no	Question	Answer
123	Favourite Colour	Blue
123	Favourite Animal	Horse
123	Favourite Car	Porsche
124	Favourite Colour	Pink
125	Favourite Animal	Dog
125	Favourite Car	BMW

Presentation Table

ID no	Favourite Colour	Favourite Animal	Favourite Car
123	Blue	Horse	Porsche
124	Pink	Not inscope	Not inscope
125	Not inscope	Dog	BMW

 

[Fazza]

Hello

For a cross-tab report, suggest you use a query. SQL could be

TRANSFORM MAX(Answer)
SELECT [ID no]
FROM your_table
GROUP BY [ID no]
PIVOT Question

Easier to set up from a new file. Save your source file, from a new file ALT-D-D-N and follow the wizard. Excel files, OK. Browse & choose. If you get a message about no visible tables choose 'options' and 'system tables' and see the source data worksheet. Choose that and continue til you get the last step then take the option to edit in MS Query. Via the SQL button change what you see to what I posted, OK to enter, acknowledge (OK) any message about not being able to graphically represent etc. See the results dataset. Via the 'open door' icon return the results to a worksheet. This is refreshable like a pivot table. The resultant worksheet can be moved into the source data file if you like.

This non-formula, non-code solution easily handles huge amounts of data, btw.

regards

 

[James12513]

Hello Fazza thanks for sharing this

The SQL query isn't working, it is popping up with this error message: Could not add the table 'source' ?

I have the data table in an excel workbook called 'source' and I have opened another excel file like you say to execute this SQL statement.

 

[Fazza]

Could not add the table 'source' suggests the source worksheet is named 'source'. Though you wrote workbook. So I'm not 100% sure what is happening.

Suggest you advise your Excel version & detail the steps you've followed to the point of failing.

Please also describe the data set up if it is different from worksheet named 'whatever', data headers in row 1 from cell A1, data immediately under (so from row 2).

 

[James12513]

Sorry I have named the workbook and worksheet "source." - The data table is from A1 to C7.

I am using excel 2007.

The excel workbook I have opened to run the wizard is called "x" and the worksheet left "sheet 1."

The SQL code I am using is:


TRANSFORM MAX(Answer)
SELECT [ID no]
FROM source
GROUP BY [ID no]
PIVOT Question

Thanks,

 

[James12513]

This is a good method Fazza -and I appreciate your post but just as I've created an excel template which follows excel functions, does anyone know a function that can also do this please?

 

[Aladin Akyurek]

This is a good method Fazza -and I appreciate your post but just as I've created an excel template which follows excel functions, does anyone know a function that can also do this please?

If the data table is not huge, try to follow the following set up.

Let Sheet1, A1:C7, house the data table, the headers included.

Select A2:C7 and name the selection as DTable.
Now define Ivec using Formulas | Name Manager as referring to:

=ROW(DTable)-ROW(INDEX(DTable,1,1))+1

Let Sheet1, E:H house the processing (your presentation table).

E2, control+shif+enter, not just enter, and copy down:

=IFERROR(INDEX(DTable,SMALL(IF(FREQUENCY(IF(INDEX(DTable,0,1)<>"",
  MATCH(INDEX(DTable,0,1),INDEX(DTable,0,1),0)),Ivec),Ivec),
  ROWS($E$2:$E2)),1),"")

F1, CSE and copy across to H1:

=IFERROR(INDEX(DTable,SMALL(IF(FREQUENCY(IF(INDEX(DTable,0,2)<>"",
  MATCH(INDEX(DTable,0,2),INDEX(DTable,0,2),0)),Ivec),Ivec),
  COLUMNS($F$2:F$2)),2),"")

F2, CSE, copy across, and down:

=IFERROR(INDEX(DTable,MATCH(F$1,IF(INDEX(DTable,0,1)=$E2,
    INDEX(DTable,0,2)),0),3),"")

 

[James12513]

Hello Aladin, I have followed your steps but the "presentation" table with your array formulas are blank?

btw the data table will be huge, around 27,000 rows

 

[Fazza]

Sorry I have named the workbook and worksheet "source." - The data table is from A1 to C7.

I am using excel 2007.

The excel workbook I have opened to run the wizard is called "x" and the worksheet left "sheet 1."

The SQL code I am using is:


TRANSFORM MAX(Answer)
SELECT [ID no]
FROM source
GROUP BY [ID no]
PIVOT Question

Thanks,

Thank you.

It might be just the syntax for referring to the source data.

For a worksheet named source, use : FROM [source$]

Hopefully that is the only change you need.

BTW, for a named ranged source you can use : FROM source
Though the system does not work on dynamic named ranges.

 

[Aladin Akyurek]

Hello Aladin, I have followed your steps but the "presentation" table with your array formulas are blank?

btw the data table will be huge, around 27,000 rows

See the workbook that implements the suggested formula system:
https://dl.dropboxusercontent.com/u/65698317/James12513%20data%20to%20presenation.xlsx

Note. The data is sizeable. If unique id's and unique questions are too many, the performance will suffer.