Return Column Names AND values from row in a reference column

KKesterson

I'm trying to figure out how to return the columns and values found in them for any given row.

I have approximately 200 unique columns that are populated from a web form if/when the column is relevant.

individual rows track construction items completed. Each row identifies crew, date, job number, etc., and the remaining columns are the construction billing units that may or may not be claimed for that day's work.

What I would like to generate is a summary of individual values from all non-blank cells in a given row that include their respective column names (which are the billing item names) as a prefix to to the value entered.

Currently, when using Join / Collect the result is an indecipherable grouping of numerical quantities without context.

Genevieve P.

Hi @KKesterson

There currently isn't a function that can automatically append the Column Name to a value, or even bring the column name into the output in any way... it would need to be identified manually in the formula. (Please provide your feedback to our Product team through this form, here!)

JOIN(COLLECT is what I would suggest, but I understand that becomes tricky when you cannot see the column names.

Would it be possible to break this intake sheet out into multiple, separate sheets afterwards (with a Copy Row workflow), so that you don't have 200 columns to evaluate? Then in each of your smaller sheets we could hide the irrelevant columns and add in the column names manually to the formula.

Cheers,

Genevieve

KKesterson

I see what you're driving at, but I'm required to send out 3rd party notifications when an entry is submitted via the web form. My ultimate goal here is to generate a truncated summary of units claimed within an automated notification after the Join(Collect summarizes the non-blank blank cells/items.

example:

Contractor submits web form entry for some units under A Category, S Category, and some from H Category.

In the sheet, I have leading columns (check boxes) indicating if any A Codes are used (followed by all A Code columns), another check box if S codes are used (followed by all S codes, etc...) I've reduced the notifications to only sending this vague checkbox reference, but I need my notifications to specify which codes are claimed, and the quantities of each (which is the value in the cell).

That's where the Column Names are needed.

I saw another recommendation to drop the following structure into the outgoing message body, but the number of columns gets cut off less than halfway through. This is meant to only display cells in that row that have a non-blank value if I understood it correctly.

" Outgoing message begins....

{{Column 1}}

{{Column 2}}

{{Column 3}}

{{etc..}} "

With that cutoff occurring, I'm at a loss...

Genevieve P.

Hi @KKesterson

Is it possible that more that one type of Codes would be checked, or will it always be either A Codes, S Codes, etc?

If it's only ever one group of Codes, then perhaps the workflow/outgoing message body is a possible solution. You would need to build out multiple, separate workflows (or separate conditions in one Workflow), each with a condition saying that "If the A Code Box is Checked" then send out the email with the correct columns included.

The other alternative would be to create one massive formula that looks to see if the box is checked for one group, and if it is, return all the values into this same cell. You would need to write in the column names into the formula though, like so:

=IF([A Codes]@row = 1, "Column 1 - " + [Column 1]@row + "Column 2 - " + [Column 2]@row + "Column 3 - " + [Column 3]@row, "")

Then you would add together multiple IF statements for each Code Group:

=IF([A Codes]@row = 1, formula, "") + IF([S Codes]@row = 1, formula, "") + IF([H Codes]@row = 1, formula, "")

KKesterson

I see where this is going. Is there a way to structure this IF statement to only return a value if there is one, and omit the item if there isn't? I'm having a hard time figuring out how that would look...

this formula returns a value that is difficult to parse at a glance:

=IF([AERIAL CONSTRUCTION?]@row = 1, "QTY A-1 - " + [QTY A-1]@row + "QTY A-2 - " + [QTY A-2]@row)

Result for zero value at A-1 & 126 value at A-2

= QTY A-1 - QTY A-2 - 126

There are 29 A-Codes to compile a value from

basically: (the following code doesn't work, but it shows what I'm trying to retrieve)

=IF(AND([AERIAL CONSTRUCTION?]@row = 1,

IF([QTY A-1]@row = <>"", "QTY A-1 - " + [QTY A-1]@row, ""),

IF([QTY A-2]@row = <>"", "QTY A-2 - " + [QTY A-2]@row, "")))

I'm trying to remove zero value returns.

Genevieve P.

Hi @KKesterson

Thank you for this additional information. You are correct, the last formula structure you have is what would need to happen in this case (since you have text that goes along with each individual cell), but the issue is you'll want to add the next IF statements together with a + between instead of a comma.

Try:

=IF(AND([AERIAL CONSTRUCTION?]@row = 1,

IF([QTY A-1]@row = <>"", "QTY A-1 - " + [QTY A-1]@row, "") +

IF([QTY A-2]@row = <>"", "QTY A-2 - " + [QTY A-2]@row, "") +

IF([QTY A-3]@row = <>"", "QTY A-3 - " + [QTY A-3]@row, ""))

Does that make sense?

KKesterson

Good morning Genevieve,

Appreciate the follow up. I've plugged the formula in and I'm getting a "#INCORRECT ARGUEMENT SET" using your example...

=IF(AND([AERIAL CONSTRUCTION?]@row = 1, IF([QTY A-1]@row = <>"", "QTY A-1 - " + [QTY A-1]@row, "") + IF([QTY A-2]@row = <>"", "QTY A-2 - " + [QTY A-2]@row, "") + IF([QTY A-3]@row = <>"", "QTY A-3 - " + [QTY A-3]@row, ""))

Genevieve P.

Hi @KKesterson

Oops! You don't need the AND at the front...

=IF([AERIAL CONSTRUCTION?]@row = 1,

IF([QTY A-1]@row = <>"", "QTY A-1 - " + [QTY A-1]@row, "") +

IF([QTY A-2]@row = <>"", "QTY A-2 - " + [QTY A-2]@row, "") +

IF([QTY A-3]@row = <>"", "QTY A-3 - " + [QTY A-3]@row, ""))

KKesterson

Getting an "Invalid Operation" error now. lol

=IF([AERIAL CONSTRUCTION?]@row = 1,

IF([QTY A-1]@row = <>"", "QTY A-1 - " + [QTY A-1]@row, "") +

IF([QTY A-2]@row = <>"", "QTY A-2 - " + [QTY A-2]@row, "") +

IF([QTY A-3]@row = <>"", "QTY A-3 - " + [QTY A-3]@row, ""))

Genevieve P.

@KKesterson

My apologies! I have not had enough coffee today... it's because of the = sign before the <>

The <> replaces an = sign, saying "is not" instead of "is"

=IF([AERIAL CONSTRUCTION?]@row = 1, IF([QTY A-1]@row <>"", "QTY A-1 - " + [QTY A-1]@row, "") + IF([QTY A-2]@row <>"", "QTY A-2 - " + [QTY A-2]@row, "") + IF([QTY A-3]@row <>"", "QTY A-3 - " + [QTY A-3]@row, ""))

This will be the correct syntax, I've tested it. However it won't have spacing between values, so you may want to add in a space before each Q:

=IF([AERIAL CONSTRUCTION?]@row = 1, IF([QTY A-1]@row <>"", " QTY A-1 - " + [QTY A-1]@row, "") + IF([QTY A-2]@row <>"", " QTY A-2 - " + [QTY A-2]@row, "") + IF([QTY A-3]@row <>"", " QTY A-3 - " + [QTY A-3]@row, ""))

KKesterson

WoooooHoooo!!!! it works!

Thank you so much for helping with this.

Now, I imagine I can repeat the same formula to group the total codes used across all categories. I'm so excited! hahaha.

Genevieve P.

Haha!! Yay! I'm glad we got there in the end. 🙂