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. betaclassusertextCriteria1Value 1Criteria2Value 2alpha2arrowClass2beta gammanullbasketbetagammaClass x-ray5flowerUserTextflowerUserText 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 🙂

Advice needed on Hlookup type of problem

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 🙂

Help

Accepted answers

danilang

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