Aligning Inconsistent Field Positions/Names

[CGIQV]

I've been able to use one of the Crew Macros and bring in a bunch of data from within a directory.  I still have some work to do with formatting this data before I can begin the calculations I need.

Below is the way data is currently positioned and the problems I'm facing:

Fruit	State	January	February	March	October	November	Total
Apple	AZ	12	13	55	12	9	101
Apple	NY	22	93	3	78	29	225
Apple	CA	23	1	96	29	20	169
Fruit	State	March	April	May	Total
Pears	NY	52	28	52	132
Pears	OR	51	86	85	222
Pears	NJ	92	86	92	270
Pears	PA	22	99	66	187
Fruit	State	September	December	2019 Total
Limes	FL	91	96	187
Limes	MA	47	31	78
Limes	CA	89	16	105
Limes	TX	30	19	49

When all is said and done, I need values for Fruit/State/Total.

Fruit	State	Total
Apple	AZ	101
Apple	NY	225
Apple	CA	169
Pears	NY	132
Pears	OR	222
Pears	NJ	270
Pears	PA	187
Limes	FL	187
Limes	MA	78
Limes	CA	105
Limes	TX	49

My two issues are alignment, and naming for the total column.  It should always contain total, so maybe some sort of contains() formula.

[ashissanpui]

@CGIQV  use arrange and summarize tool to get that.

[danilang · Accepted solution]

Hi @CGIQV 

The problem with these kinds of problems is that the columns change from month to month.  You currently have the five month columns, but in the future you may have 4, 6, and the month names may change.  Because of this inconsistency, you need to have a solution that is is dynamic and never references the month column names.  The transpose tool is ideal for this, because it uses Dynamic or Unknown Columns to ensure that any changes to the data columns are included

First you add a RecordID to sort your final data.  Remove the leading and trailing spaces and the header rows.  Then transpose the quantity columns using the RecordID, Fruit and State as key columns.  After converting the quantity column to integer, sum up the quantities by Fruit and State. After sorting and removing the RecordID column, you're left with

Dan