Index Function & OffSet Function combination

[BhatiaSakshi]

Hello All,

Happy Easter!!

I am struggling with one query in Alteryx and it is quite huge to post here so I am attaching sample data for the reference. In attached file I have two tabs first one where I need to input the data. In reality these are two separate files. I have mentioned the cell references in expected output for understanding.

Please help.

Thank You in advance.

[BhatiaSakshi]

Hello All,

Did anyone see the query?

Thank You,

Sakshi

[grossal]

Hi @BhatiaSakshi,

will you have more Countries (columns) or Codes (rows) in the future or will the all the data always have this exact shape?

It's probably pretty easy to find a solution that works on this dataset, but it will be hard if we need to make it dynamic. I'll work on a solution when this is clarified. 

Best

Alex

[BhatiaSakshi]

There are many Codes and Countries. Its just I have given only 2 countries here for reference. but one thing would be same that the format will remain same. In excel where I need to apply this workflow has almost 28 countries and 35 codes.

[BhatiaSakshi]

 Did you get the chance to look at below query?

[grossal]

I have it on my list for tonight.

One more question: How do you identify the right "row" or will the row always be in the same position?

[BhatiaSakshi]

Ok...let me explain the query so what I need is look up combination of Code & Country which will decide the row like china with code "R001" exist in cell X11. now which articular column will be picked up is basis the header. Please let me know if there is any disconnect.

Thank You,

Sakshi

[grossal · Accepted solution]

Hi @BhatiaSakshi,

I think I understand what you mean by now. I got a rough draft for you:

Output:

What's missing:

- Filter out not needed columns

- Rename Columns to match required column-name

- Do the Multiplications

Workflow attached. Let me know what you think.

Best

Alex

[BhatiaSakshi]

Thanks a lot for quick turnaround...I will apply this in my existing workflow...just one question you have filtered Country "India". DO I need to follow this step for all countries I have in my data. 

Sorry to trouble you but being fresher I need more insight.

[grossal]

It doesn't matter what country you pick at that point. I just wanted to make sure all "columns" appear only once in my list, therefore I picked one country to achieve this. Unique Tool didn't not work because column names start repeating after a while within one country.

There are many better ways to achieve the same, but I just tried to get it "working" in that moment. You could for example count up and remove all after 21 (you have 21 data columns). This would also guarantee that all appear only once.