Insert Excel rows for each x in adjacent country columns to prepare Oracle Calendar Event Coverage uploadsheet

Question

I am uploading Calendar Events in Oracle Fusion. The events went fine, but I am now working on assigning the coverage which is set by country in our setup.

My coverage source data (this is sample of it), delivered by a colleagues is structured as:

Short Code2	Name2	NL	DE	GB	FR	LU	ES	AU	CA	CL	US	FI
NYD1-23	New Year's Day 2023	x	x		x	x	x					x
NYD2-23	New Year's Holiday 2023			x				x	x	x	x
EP-23	Epiphany 2023						x					x
MLK-23	Martin Luther King Day 2023										x
ADH-23	Australia Day Holiday 2023							x
LB-23	Lincoln's Birthday 2023										x
PRES-23	Presidents' Day 2023										x
ADH-23	Family Day 2023								x
STJ-23	Saint Josephs day 2023						x
EAT-23	Easter Thursday 2023						x

I could alter the data, to include helper columns or anything that would make the task easier.

What Oracle needs in the uploadsheet for coverage is the following format:

Short Code	Coverage Node	Coverage
NYD1-23	NL	I
NYD1-23	DE	I
NYD1-23	FR	I
NYD1-23	LU	I
NYD1-23	ES	I
NYD1-23	FI	I
NYD2-23	GB	I
NYD2-23	AU	I
NYD2-23	CA	I
NYD2-23	CL	I
NYD2-23	US	I

etc.

So the Short Code needs to be repeated on each row, for the amount of x's that are on the row. The country codes need to each be on a separate row and the third column is always I (for include).

So far, I've been wrecking my brain with MOD, ARRAY, TRANSPOSE and FILTER functions in Excel but I think I'm going about it the wrong way. There must be an easier solution that I am not seeing.

I'm using Excel on 365. (Version 2202 Build 16.0.14931.20858)

Help much appreciated.

Answer 1

I created a table (insert > table) from your data and called it "data".

You can use this formula:

=LET(cntCountries,COLUMNS(data),
cntRows, ROWS(data)*cntCountries,

shortCodeByRowλ,LAMBDA(r,INT((r-1)/cntCountries)+1),
countryCodesByRowλ,LAMBDA(r,MOD(r-1,cntCountries)+1),

shortcodes,MAKEARRAY(cntRows,1,LAMBDA(r,c,INDEX(data[Short Code2],shortCodeByRowλ(r)))),
countrycodes,MAKEARRAY(cntRows,1,LAMBDA(r,c,INDEX(data[#Headers],1,countryCodesByRowλ(r)))),
selection,SUBSTITUTE(MAKEARRAY(cntRows,1,LAMBDA(r,c,INDEX(data,shortCodeByRowλ(r),countryCodesByRowλ(r)) )),"x","I"),

FILTER(HSTACK(shortcodes,countrycodes,selection),selection="I"))

The basic idea is to create each column based on MAKEARRAY. The values are each retrieved based on the row of the new array - either from the short code column or from the header country codes. --> using the two LAMBDA-functions.

for the result the HSTACKEd array is filter for the I rows.

There are two lambdas to retrieve either the short code or the country code for the row.