Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #372: A Header Reference Dataset from Excel Header Reference Letters

AYXAcademy
Alteryx
Alteryx

 

A solution to last week's challenge can be found here.

 

This challenge comes to us from @caro_smith . Thank you for your contribution, Caro!

 

 

Challange_372_Image.pngWhen you move an Excel process to Alteryx, often it is necessary to convert Excel formulas so they can be used by Alteryx tools. Each column in an Excel workbook is referenced by a letter or letters: A, B, C... Z, AA, AB... AZ, etc. These letters can also be used in Excel formulas.

 

Instead of constantly searching for the column each formula references, we will use Alteryx Designer to create a list of the column header names and their associated header reference letter or letters.


In this challenge you will start with a dataset that contains only the column names. Yes, you read that correctly—no records, just headers!


Your task is to create a new dataset with two columns. The first column should include the header name as the value for each row. The second column in the solution should contain the corresponding reference letter or letters for each of the original header names. 

Luke_C
17 - Castor
17 - Castor
Spoiler
Luke_C_0-1684765231379.png

 

ahsanaali
11 - Bolide
Spoiler
ahsanaali_0-1684765328862.png

 

binuacs
21 - Polaris
Spoiler
binuacs_2-1684766692397.png

 

Kenda
16 - Nebula
16 - Nebula
Spoiler
Check out the Formula tool to see where all the magic happens!

Kenda_0-1684766503726.png

 

LynnPätzold
8 - Asteroid

 It ain't pretty, but I made it;) 

Spoiler
Sol.PNG

 

dsandmann
8 - Asteroid

Hi -

 

Most of the work here is done in the formula tool converting the digits into columns headers.

 

Spoiler
dsandmann_0-1684767891905.png

 

I previously solved this in the context of making a new dynamic macro for excel outputs that populates the range for you based on the size of the data and the starting point for the sheet, and handles 3 letter column headers as well.

 

Here is a link to the prior post that discuss the conversion from and to letters/numbers

Blake_peterson
6 - Meteoroid
Spoiler
 

Great lesson to learn more about the Multi-Row Formulas tool!

 

 #372.PNG

patrick_digan
17 - Castor
17 - Castor
Spoiler
patrick_digan_0-1684768658077.png

 

RolandSchubert
16 - Nebula
16 - Nebula
Spoiler
372.jpg