How to tranpose data and possibly concatenate custom format.

[Dexter2]

Hi there,

I have a data set, consider it to be the first table on the far left. A helper column was inserted because I could not think of any other work around. I am trying to take that data and transform it into another table, that I have partially constructed in table 3 (the far right).

My goal is to have:

1) Each location, summed, counted, and duration broken down as shown for example (0h01 - 2), if there is more than one value.

2) However, also, I would like to not have repeated values (as you may see 0h01 is shown twice).

3) Lastly, I would like a formula in cell A12 that yields the result in M7.

The middle table is what I was "playing" around with.

If you have any insight, is this possible to accomplish? Appreciate your time in viewing.

[Bo_Ry]

Try at N2

=LET(ts,SORT(FILTER($C$2:$C$11,$A$2:$A$11=$L2)),t,INDEX(UNIQUE(ts),COLUMNS($N2:N2)),IFERROR(TEXT(t,"h\hmm - ")&SUM(--(ts=t)),""))

[Dexter2]

Thank you. The format looks good, it would be "2" though for counting.

[JeteMc]

For another possibility, you could get similar information from a pivot table by:
1. Placing Location in the Rows area
2. Placing Duration in the Columns area
3. Placing Duration (Sum) in the Values area (formatted h"h"mm)
4. Placing Location (Count) in the Values area
Let us know if you have any questions.

[Dexter2]

Just tried this. How do you display the other values to represent the breakdown for NY (0h01 - 2, 0h15 - 1)?

[Dexter2]

I added duration to rows on top of it, which broke it down, but then its vertical and it won't let me format it as h"h"mm.

[Dexter2]

I'd like to apologize. I never dragged the formula over, Bo_Ry that was all correct. JeteMC, similarly will show in a new thread, there is a similar issue in the pivot table regarding post #6. Thank you both I do appreciate it.

[Dexter2]

My apologies again, I just caught this. Highlighted in yellow, you see how the amount repeats, would this be a trailing space issue in the locations?

[JeteMc]

It would be easier to troubleshoot the issue if we could see the Excel File, even with location anonymized (i.e., location1, location2 etc.)

[Dexter2]

How's this?

[Bo_Ry]

Try

G2
=LET(ts,SORT(MROUND(FILTER($B$2:$B$1159,$A$2:$A$1159=$E2),"0:1")),t,INDEX(UNIQUE(ts),COLUMNS($G2:G2)),IFERROR(TEXT(t,"h\hmm - ")&SUM(--(ts=t)),""))

[Dexter2]

Thank you very much. Is there a way to filter or sort these locations without affecting the formulas, or is better to copy and repaste as values in a new sheet, retable than sort?