Mapping Closed and Open items from another worksheet into another worksheet

Cayla Davis

I am trying to take open and closed items from one worksheet and plug in the totals based creation date and closed date into cells within another worksheet and separate them out based on airline and NDC or Graphical selected in the row

Does anyone know how to do this? I'm struggling

Data document: Amadeus: TC Known Issues & Limitations Master
- Gather opened from Created Date column
- Gather NDC or Graphical from NDC/Graphical column
- Gather Airline from Airline column
- If Closed Date is MONTH YEAR calculate by airline
- If no Closed Date, calculate the number by airline

Is this even possible?

Erin Horiuchi Green

Hello @Cayla Davis ,

I am happy to help you.

Please email me at erin.horiuchi@syneoshealth.com with availability options in EST.

To ensure privacy and streamline troubleshooting, I recommend quickly mocking up with some fake data.

There is more than 1 way to your solution and I always prefer having a selection of options to consider scalability and sustainability.

Paul Newcome

Are you able to provide some screenshots for context / reference?

jmyzk_cloudsmart_jp

Hi @Cayla Davis

Suppose the data structure is like this;

https://app.smartsheet.com/b/publish?EQBCT=238205fcf2474d27bec9286595870718

I added two helper columns;

[Closed Month] =IF(ISDATE([Closed Date]@row), YEAR([Closed Date]@row) + "-" + RIGHT("0" + MONTH([Closed Date]@row), 2), "Open")
[Joined] =JOIN(Airline@row:[Closed Month]@row, "/")

I use the first for your "If Closed Date is MONTH YEAR calculate by airline."
The second is to get a distinct Airline/[NDC/Graphical]/Closed Month combination.

Then, I made a Mapping Sheet with the following formulas;

[Joined] =IFERROR(INDEX(DISTINCT({AL N/G CM}), [#]@row), "")
[Air Line] =IF(ISTEXT(Joined@row), LEFT(Joined@row, 2))
[NDC/Graphical] =IF(ISTEXT(Joined@row), MID(Joined@row, FIND("/", Joined@row) + 1, FIND("/", Joined@row, FIND("/", Joined@row) + 1) - FIND("/", Joined@row) - 1))
[Closed Month] =IF(ISTEXT(Joined@row), SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Joined@row, "/", ""), [Air Line]@row, ""), [NDC/Graphical]@row, ""))
[Count] =IF(ISTEXT(Joined@row), COUNTIFS({Airline}, [Air Line]@row, {NDC/Graphical}, [NDC/Graphical]@row, {Closed Month}, [Closed Month]@row))

https://app.smartsheet.com/b/publish?EQBCT=15d96ac702844746a0c2cb7f55c95222

The tricky part of creating the sheet is that we want to update the Air Line, NDC/Graphical, and Closed Month values to populate automatically as the data in the datasheet changes. So, I used the Joined value created in the datasheet to map those values.

Once we have mapped those criteria values, we can count the matching issues using the COUNTIFS function.

Alternatively, we can use a report's group and summary features to accomplish the same.

https://app.smartsheet.com/b/publish?EQBCT=e2f12424eba5417185d0818948be3514

To hide the individual rows when expanding all, we can use the report widget of a dashboard.

https://app.smartsheet.com/b/publish?EQBCT=238205fcf2474d27bec9286595870718 (A dashboard showing all the sheets and reports.)