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

Question

Calendar events are being uploaded to Oracle Fusion. The events went off without a hitch, but I am currently working on assigning the coverage, which is determined by the country in our arrangement.

This example of my coverage source data, which was provided by a colleague, is organized as follows:

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 upload sheet 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 number 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 wracking 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)

Answer 1

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

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 fundamental concept is to base each column on MAKEARRAY. Depending on the row of the new array, the values are either pulled from the short code column or the header country codes. employing both LAMBDA functions.

The HSTACKEd array is filtered for the I rows in the result.

To retrieve either the shortcode or the country code for the row, there are two lambdas.