Excel question: transform data table from landscape to portrait

Factual Questions
1

I have a listing of a couple of thousand skus and their national total inventory quantity by month.
For budgetary purposes I need to partition the national totals into four warehouses.

I know the BP proportion by whse by sku.
I can simply calculate the individual whse quantities using a formula into four separate columns
(the final table needs to include time parameters eg skuwhsemonth*week qty)

But I need to transform the table essentially from landscape with one row for each sku and four whses and 12 months per row into a portrait table where there are rows per sku per month per week each with one whse value

so: (ignoring the time parameter)
From:
SKU1 National 100
To:
SKU1 WhseAA 15
SKU1 WhseBB 45
SKU1 WhseCC 25
SKU1 WhseDD 15

I have a couple of techniques I use to do this transformation depending on the scale but methinks they are just clunky either with c&p or with pivot tables. There must be a SDMB Excel user who has a nifty technique to perform the task more efficiently and elegantly.

2

I’m having some difficulty in parsing exactly what it is you are trying to do, but if I understand it, a pivot table is the “nifty technique to perform the task more efficiently and elegantly” that you are looking for. Although they look intimidating a first (and the way Excel implements the structure of pivot tables is not a model of clarity), they are actually quite powerful in doing these kind of transformations and slicing/filtering/organizing data. Any other way of doing this is going to be a kludge of creating a bunch of cell functions and sorts that will likely break the first time you make any change.

Stranger

3

Yes, I use pivot tables constantly.
The transformation here is going to be from one row per sku with (4 whses * 52 weeks) columns into 208 rows per sku.
If I’m going to drag and drop that many columns then the chances of getting some in the wrong sequence is rather high.

4

I’m not sure what you want to do, but does Excel’s transpose command do it?

5

No, alas. :cry:

Doing that will produce a table with 208 rows and over 2,000 columns with the row parameters now showing as column titles.

6

I find this -

You can use the ADDRESS function to obtain the address of a cell in a worksheet, given specified row and column numbers. For example, ADDRESS(2,3) returns $C$2. As another example, ADDRESS(77,300) returns $KN$77.

So you can expand and get 4 rows per SKU by using mod(4) of the row number; not an expert in Excel by any means, but…
So to get SKU in sets of 4, have a second tab that references the first tab sheet?
A … B Col A is a simple counter, hide it when done. Col B contains value of other sheet col 1.

1 =Sheet1;address(MOD(A1+3,4),1 ) - which should return mod (4,4)=1 the contents of $A$1 in the first sheet.(The SKU I assume?)
2 =Sheet1;address(MOD(A2+3,4),1) ditto mod (5,4)=1
3 =Sheet1;address(MOD(A3+3,4),1) ditto mod (6,4)=1
4 =Sheet1;address(MOD(A4+3,4),1) ditto mod (7,4)=1
5 =Sheet1;address(MOD(A1+3,4),1) -which will now give you mod(8,4) =2 so $A$2, the second SKU
6
7
8
9
…etc.

I assume if you do the content once and copy cells down, it will increment like most excel formulas?
If you need a row per week and warehouse, then it’s mod 208?

Just a thought.
But, you’ll get a ton of formula, big sheet.
Once you have what you need, “paste values” to another sheet where it won’t spend forever recalculating every time you touch something…

7

So each row is one SKU followed by a series of quantities divided into the warehouses, repeated ‘horizontally’ (in the row) for each weak of the year, and you are trying to arrange each into individual rows organized by month, then subdivided by week within the month, and then further subdivided by each warehouse?

If this is the case, I think what you are trying to do is beyond simple functionality in Excel. You’re either going to have to write some VBA routines to automate reorganizing the data, or take it into another tool. I would personally use Python and Pandas to do this because of how easy it is to vectorize data handling functions in Python, and there are probably some clever ways you can use to do all of this within Pandas dataframe operations, but of course you’d have to be comfortable using Python and Pandas or spend the time to get up to speed.

Stranger

8

Now that has distinct possibilities. Excellent. Thank you.

My current approach is to use multiple pivot tables to progressively swing the columns into rows.

After 3 transformations I get a data table with over 250k rows and a handful of columns. Just as Power BI likes it.

Hence my appeal to wiser and/or more laterally thinking minds. :slight_smile:

9

My favorite Excel trick is using the ADDRESS() function with internal formulas to figure out a cell reference, and the INDIRECT() function to get the value for that cell reference. Building on md-2000’s example

Cell for transformation would have indirect(address(mod(a2+3,4),1,1,1)) etc. Some guidance for indirect() https://exceljet.net/functions/indirect-function

10

Oops, in my example, 5th row should be:

5 =Sheet1;address(MOD(A5+3,4),1)

If the sheet ends up being too big, you can do it incrementally.
Create the first two columns only, SKU and warehouse.
Paste values that, then used VLOOKUP to find the numbers from the SKU in col A to calculate per warehouse quantities, etc.
Alternatively, create a sheet for each week, once you have the SKU’s in columns.
Then copy and paste each sheet together into one, one below the other, sort by SKU.

Seriously, this is the sort of thing that begs for a database solution like Access or (good old days) dBase.