Dynamic List Formula

[bfolliard]

Hello Alteryx Community,

I have a specific problem that I couldn't find in the other topics. I have a simple data set that I am applying simple formulas to (in this example summing). However, There are several inputs that determine what would need to be summed. In the below example, the result would sum each of the entities in teh given list (eg List1 would be entiy 1 (50) + entity 2 (25) + entity 3 (75) + entity 4 (25) = 175. See my data set and various inputs below:

DATA SET:

Entity	State	Amount
1	AL	50
2	AR	25
3	IL	75
4	IL	25
5	AL	125

STATE INPUT

State	Grouping
AL	List1
AR	List3
IL	List2

LIST 1

Entity

1

2

3

4

LIST2

Entity

1

2

3

LIST3

Entity

1

3

5

DESIRED RESULT:

List	Amount
List1	175
List2	150
List3	250

To take this one step further, I am looking for a way for the process to be dynamic enough so that if a new "List" was added, it would automatically be incorporated without having to copy/paste the steps to account for it. I have a workbook where each of the lists is a different tab so I was thinking I could get the list of tabs but don't know where to go after that. I have been struggling with this for a while. Any ideas? Thanks in advance for any assistance you're able to provide!

[PeterA1]

@bfolliard The easiest way to do this might be a with a multi-row formula tool. You could use a Find Replace to append the values to your data set for each "list #" Then the multi-row formula (tool mastery article) can be configured to "group by" the "List#" which should allow you to sum the values by making a formula like

If ISEMPTY(CurrentRow -1) - checking if we are in the top row of a list  then "CurrentRow" else "Current Row"+"[New Field] -1" endif

Then the last value for each list will be the total for that list. You can then sort, sample etc to isolate that value and ultimately Join back to list names with a select tool or display however you see fit. 

This whole process could also be done with a batch macro where: 

List Names are the "For Each" grouping variable and then the process inside would be similar to what is described above - the multi-row formula and some sort of Sort/Filter/Select tool to isolate the final totals and display the data you want.

Alternatively - if you can somehow get the List#'s to appear next to each item, and combine all of the lists so you have a column of "List#', Column with Entity, and a column with value, you could just use a summarize tool to "Group By List, Sum values

Sorry if that was a bit confusing! Hopefully this helps

Best,

Peter

[kelly_gilbert · Accepted solution]

I wonder if you could perhaps union the list/entities into one dataset? Something like this:

List	Entity
List 1	1
List 1	2
List 1	3
List 1	4
List 2	1
List 2	2
List 2	3
List 3	1
List 3	3
List 3	5

Then, you could simply join that to the State/Amount dataset on the entity ID.

To automate creation of the unioned list/entity dataset, you could use a Dynamic Input tool (see this article for instructions). Note, the format/schema for each tab in your workbook needs to be exactly the same for the Dynamic Input tool to work. If that's not the case, then you'll need to follow the second half of the linked article to create a macro.

[bfolliard]

Hi Kelly,

Thanks for the response! I took your suggestion. I started by getting all the tab names from the workbook I am working in and then added a macro that takes reads in the data from each of the tabs. From there it functions very similar to your idea about unionizing the lists. Appreciate the help!