Advice needed on Hlookup type of problem

[davidoc]

Hello,

I have a spreadsheet with large number of columns including 15 criteria cols. I need to create a column (such as Value 1 example below), where the contents are the value in the same row as the header specified in Criteria 1 .  There are over 50 columns with 15 different criteria columns.  Each row has different data values in each coiumn.

beta	class	usertext	Criteria1	Value 1	Criteria2	Value 2
alpha	2	arrow	Class	2	beta
gamma	null	basket	beta	gamma	Class
x-ray	5	flower	UserText	flower	UserText

so in value 2 the first row would be  alpha, the second would be null, the third flower.

I am stuck with working out what tools would be required.  Any assistance appreciated 🙂

[LordNeilLord]

Hey @davidoc 

In situations like this, you should add a recordid and transpose your data...it will make it a lot easier to work with

[john_miller9]

@davidoc 

Take a look at the attached and see if this gets you there.

[davidoc]

Thanks, really appreciate teh quick turnaround - I will try and let you know how It works (probably Monday as busy till the end of the day) 🙂

[smoskowitz]

I would also add a record Id and then transpose by record ID. Do what you need to do and then crosstab back to the original structure -- if desired.

[davidoc]

Thanks!

[danilang · Accepted solution]

Hi @davidoc 

@john_miller9's solution is close, but it has a couple of problems. 

1. The formulas he uses to create the "Value x" only handles single digit criteria so will create duplicate value name columns if you have more than 9 criteria

2. The final order of the columns is set manually in the final Multi-Join.  As you add more criteria you would have to change the ordering in the multi-tool

This workflow is completely dynamic handling any number of criteria.

   

It starts by adding a record Id and the crosstabbing all the the columns.  Next comes a column ID so that we can restore the original column order after the cross tab.  The column ID increments by 2 so we can slot the new Value columns in between the Criteria ones.  The Join does a self join on RecordID and "Value"="Name".  This provides the value from the column specified in the Criteria X columns.  This pulls out a new set of records that represent the new columns we want to add.  The next formula tool replaces "Criteria" with "Value " and increments the ColumnID by 1 so that it comes immediately to the right of the Criteria column.  This new group is unioned back with the original one.  The cross tab uses the ColumnID field as the name field so the columns come out in the correct order.  The bottom branch here, sorts by RecordId and columnID and select the records from the 1st Record to gives us a list of Field names in the proper order.  The dynamic rename then renames the numbered column to their corresponding name from the list coming into the R input

Dan

[davidoc · Accepted solution]

Thanks for the solution - really appreciate the support!