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

0 votes

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)

Jan 14, 2023 in Others by Kithuzzz
• 38,010 points
270 views

1 answer to this question.

0 votes

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.

enter image description here

answered Jan 14, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to apply zoom animation for each element of a list in angular?

Hey @Sid, do check if this link ...READ MORE

answered Jul 30, 2019 in Others by Vardhan
• 13,190 points
1,193 views
0 votes
1 answer

Want to compare two columns in excel

Hello To compare two columns in excel ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
855 views
0 votes
1 answer

How to import excel file in Oracle SQL live

Hello, there are a few steps You'll ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
1,651 views
0 votes
1 answer

Convert Rows to columns using 'Pivot' in SQL Server

If you are using SQL Server 2005+, ...READ MORE

answered Jun 20, 2022 in Others by nisha
• 2,210 points
3,882 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,233 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,418 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
696 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,325 views
0 votes
1 answer

Transposing columns to rows in Excel

Due to the amount of your data, ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 63,420 points
736 views
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
498 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP