Aligning Inconsistent Field Positions/Names

Question

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:

FruitStateJanuaryFebruaryMarchOctoberNovemberTotal  AppleAZ121355129101  AppleNY229337829225  AppleCA231962920169  FruitStateMarchAprilMayTotal    PearsNY522852132    PearsOR518685222    PearsNJ928692270    PearsPA229966187    FruitStateSeptemberDecember2019 Total     LimesFL9196187     LimesMA473178     LimesCA8916105     LimesTX301949

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

FruitStateTotalAppleAZ101AppleNY225AppleCA169PearsNY132PearsOR222PearsNJ270PearsPA187LimesFL187LimesMA78LimesCA105LimesTX49

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

danilang · Accepted Answer

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