Extracting excel cell value for a specific (variable) column

[Thomas_Laszlo]

What is your goal?

Finding out the right column in an excel file (where the column-position always changes)

What is the problem?

I have an export of a tool in excel format, where the column sorting is always different each time i do the export (Perspective crm = Crazy tool )

Example:
1st export: column1=name, column2=title, column3=email
2nd export: column1=email, column2=name, column3=title

How could I find out, which column holds the title “email” in the first row and then, when iterating through the rows, always refer to the right column?

[Marie]

HI,
Could you share how the output of your excel module looks like and how your Scenario is build currently?

A Solution that might work depending on the Setup of your export and Scenario.:

1. Only get the first Row from your spreadsheet.
2. Set a Variable that determines per Functions in what cell “email” is written. Logic example "If A1 Contains “email” return “A”; If B1 contains “email” return “B” …
3. Then you get the actual range from your spreadsheet that you need and use the Variable you have set to know in wich Collum the emails are.

[Flow.Digital]

Hey @Thomas_Laszlo

Here’s the simple approach:

Module 1: Excel > Search Rows

• Range: A1:Z1 (just the header row)

Module 2: Excel > Search Rows

• Range: A2:Z1000 (your data rows)

Module 3: Iterator on the data rows

Module 4: Use Make’s built-in get() function

When mapping values, instead of hardcoding column positions, use:

• {{get(4.values; indexOf(1.values; "email"))}}

This finds where “email” is in your header row, then pulls that same position from each data row.

So whether email moves to column 2 or column 8 in the next export, it’ll always grab the correct value by matching the header name instead of column position.

Sam @ Flow Digital
Sr Automation Specialist

[Thomas_Laszlo]

I finally found the solution myself. I create an array of all column headings (field names) with the corresponding column identifier (A…AZ). When accessing data in excel afterwards, I can get the correct cell by naming the header and the function uses the correct column identifier.

This is the workflow:

Here is the scenario in JSON:

Find Excel Rows old.blueprint.json (183.3 KB)