Formula/VBA to Vlookup multiple values both horizontally and vertically

Hello,

I have a problem output from one of our databases...

Essentially it is built so that multiple outputs from single clients can span multiple Rows with each row giving different details about that single client.

I want to be able to transform the data either through a Formula (would be best) or VBA - but having a new tab show the unique client ID just once on its own row, but then the various data that may appear for that client to be shown horizontally over a number of columns.

Each client may appear just once in the initial extra, so will only have a matching value in one of the horizontal columns in the end result, or they may appear 200 times with 200 various peices of information that I would need to span over the 200 columns on the end results.

I do have some VBA for this, but I am not sure how to alter it or come up with a formula variant.

My current VBA code is as follows...

Please Login or Register  to view this content.

However, this will only output as many columns as appears for the person - I ideally want it to pull through the Row values based on a set list of headers, and fill in the matches it fails on with a 'N/A' value.

I have attached a test spreadsheet to show what I am looking to get to, but just cannot see what I may need to change to get to it.

Hopefully someone is able to help, and I would be most grateful if any is able to.

Hi,
please try in A2 and down:
=IFERROR(INDEX(Extract!$A$2:$A$13,AGGREGATE(15,6,(((ROW(Extract!$A$1:$A$12))/(MATCH(Extract!$A$2:$A$13,Extract!$A$2:$A$13,0)=(ROW(Extract!$A$1:$A$12))))),ROWS(Extract!$G$1:G1))),"")

B1 and drag to the right:
=INDEX(Extract!$B$1:$B$13,AGGREGATE(15,6,((ROW(Extract!$B$2:$B$13))/(MATCH(Extract!$B$2:$B$13,Extract!$B$2:$B$13,0)=ROW(Extract!$B$1:$B$12))),COLUMNS(Extract!$H$1:H1)))

B2 and across for the numeric values:
=IFNA(INDEX(Extract!$C$2:$C$13,MATCH('Desired Result'!$A2&'Desired Result'!B$1,Extract!$A$2:$A$13&Extract!$B$2:$B$13,0)),"")

Belinda200 - your solution worked perfectly thank you.

Thanks for your help everyone.

Thanks for letting me know, you're welcome.