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

 

[James12513]

Aladin, Ah that's genius how that works! thank you!

@Fazza would also be nice to get your method working, I'm probably doing something stupid

 

[Aladin Akyurek]

Aladin, Ah that's genius how that works! thank you!

@Fazza would also be nice to get your method working, I'm probably doing something stupid

You are welcome.

 

[Fazza]

I'm guessing you're close to having it work, James. If you explain exactly what step fails it should be simple to sort out. regards

 

[James12513]

Hi Fazza

It works an absolute charm! :D

I was being stupid, should have used the following:

TRANSFORM MAX(Answer)
SELECT [ID no]
FROM TRANSFORM MAX(Answer)
SELECT [ID no]
FROM `C:\Users\James\Desktop\source.xlsx`.`source$` `source$`
GROUP BY [ID no]
PIVOT Question
GROUP BY [ID no]
PIVOT Question

If you change the source data, is there no way the "presentation data" workbook can update automatically without having to run the SQL query again?

I changed the data in the "source" excel file but it only deleted the data in the "presentation workbook", i.e. if I changed ID no. 123 - favorite colour to Yellow in the source excel sheet, it would update the answer in the "presentation" excel sheet as blank.

Thank you!

James

 

[Fazza]

That SQL isn't exactly what I was expecting, James. However, to your questions.

If you change the source data. Not clear exactly what you mean. Similar to a pivot table, for the 'presentation data' workbook to update there must be a refresh operation: it is not automatic as formulas would be (when a workbook is in the normal auto-calculate mode). The refresh can readily be set to occur on opening of the 'presentation data' workbook or every n minutes - these are simple to change/set properties of the query table. And VBA offers other approaches.

Maybe the data refresh isn't understood? I don't know. It is a refresh like a pivot table refresh. There is no step to re-visit the SQL, just do the refresh. If doing this manually I'm a strong keyboard user so would select a cell within the results and press ALT-D-R.

I'm not clear about the favorite colour changing to yellow in the source sheet & then blank in the results. Like you, that doesn't sound right to me. Difficult to troubleshoot from here, sorry.

What you have so far is a scratch on the surface of what can be done with external data queries. If different things are required please google for further information, ask again.

regards