Help with V lookup Function with Multiple Criterias

[AlexOdou]

Hello Everyone!

I receive a weekly excel file with more than 500 rows with multiple entries for a particular pt ID on specific date with data for right, left and both eyes; I need to re-structure it from a column orientation to a row orientation. I thought that using a v lookup will help me to accomplish, but it is more complicated than that. (don't know if i'm explaining myself clear),but I attached a sample of the data for anyone willing to help.

I receive the data the same as table 1 and need to change it to looks like table 2.

from table 1 the laterality column become the header and corresponding values gets transpose from column orientation to row orientation.


Thank you in advance for any help or guidance on how to deal whit it.


Respectfully,

Alex

[AliGW]

Alex - firstly, there is no such product as Office 360. I presume you have Office 365 (days in a year, not degrees in a circle) - you might wish to update your user profile accordingly.

Secondly, this can be done in PowerQuery. Select the table and load it into PQ, then pivot on the left/right column using the column to its right as the value - set to no aggregation.

[AlexOdou]

Hi AliGW,

Thank you very much for pointing out my profile error, corrected already.

Thank you for your suggestion but i never used Power Query before, I guess I will have to learn it and see if I can follow your instructions.

Respectfully,

Alex

[AliGW]

OK - here's what you need to do:

1. In the original workbook (not my copy) select the whole of your source table (A2 to D23).
2. With this range selected, go to the Data ribbon and click on From Table/Range in the Get & Transform area of the ribbon (on the left). This will load the table into PowerQuery.
3. Select the Laterality column by clicking on its header, then on the Transform ribbon, select Pivot.
4. In the dialog that opens, make the Values column Visual Acuity and in the Advanced Options choose Don't Aggregate - click OK.
5. Finally, click on the downward arrow of the Close & Load button at the far left of the Home ribbon - choose existing sheet and click on the cell where you want the table to appear.

All you need to do now is remember to refresh the table when changes are made to the source table (Refresh on the Data ribbon). This can be automated by setting auto-refresh in the query properties. To access the query properties, click Queries & Connections on the Data ribbon, then right-click the query in the pane that appears to the right and choose Properties.

You can step through the stages set up in the query to see what was done to reshape the data.

[AlexOdou]

Good Morning AliGW,

You got the results I was looking for! and your instructions are very easy to follow, but in my version of Excel I don't see the "From Table/Range in the Get & Transform area" of the ribbon. I do have "Get External Data", Connection", Sort and Filter", Data Tools and outline.

Respectfully,

Alex

[AliGW]

It must be there - you have Office 365.

[AlexOdou]

Hi,

I have it at home, but at work I have Microsoft Office Professional Plus 2013.

But the IT already installed on my PC!

will follow your instructions.

Thank you Very much for your invaluable help!!

Respectfully,

Alex

[AliGW]

OK - so if the IT guys have now installed PQ for you, you should be able to find the options on the PQ add-in ribbon.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[AlexOdou]

I will, but it have been working for 20 min now and not result.

I'm doing something wrong?

Respectfully,

Alex

[AlexOdou]

still loading....

[AliGW]

Impossible to say - I am not clairvoyant.

If you would like to give more details of what you have done, what has happened and what has gone wrong, then maybe I can try. Bear in mind I am not working with the PQ add-in, so there will be some subtle differences in the interface.

[AliGW]

still loading....

How many rows of data are you trying to process??? 500 or so should not cause any issues.

[AlexOdou]

Not, i'm just trying the sample i sent first.

[AliGW]

It should not be causing any problems. You must have done something wrong. It processed instantly here.

[AlexOdou]

Definitely,

will make a new sample and start all over again.

Thanks for your help and time, i really appreciate it!!

Respectfully,

Alex

[AliGW]

OK - let us know how you get on.

[AlexOdou]

Hi,

Still having problem with the PQ on my work computer running Office Professional Plus 2013, the IT is investigating.

anyway, using my personal comp running the 365, I was able to load the test into the PQ, but for the next step :

3. Select the Laterality column by clicking on its header, then on the Transform ribbon, select Pivot, in the transform ribbon i don't see "Pivot"

Thank you very much for your help.

Respectfully,

Alex

[AliGW]

On the Transform ribbon in the PQ workspace - it's about halfway along.

[AlexOdou]

Found it!

It works perfectly!

Thank you very much for your patient with me. you have been wonderful!

The PQ is a powerful tool, i need to master it.

thanks again for pointed me to this new tool. i will mark this threat as solved.

Respectfully,


Alex

[AliGW]

It's a real Pandora's Box! Glad to have helped.