generate list of equipment use from a spreadsheet calendar

[striker]

Hi Everyone, it has been awhile... 20 yrs.

My company uses Excel to track equipment usage for consulting clients. There is a sheet showing sequentially assigned Booking Ref numbers YY-NNN (not shown here) and a calendar view (below) where those same YY-NNN strings are placed in cells for each day that particular piece of equipment is being used.

What causes the equipment manager grief is that they have to confirm with project managers the actual usage and get charge-out sheets to the billing department.
If a separate tab of the sheet with the following could be generated it would make this task much easier.

I realize the results could be more elegant/compact with concatenation, but the output here will work nicely to paste into charge out sheets. It gets more complicated due to contracts and day/week/month rates from there.
The Booking Ref values should be grouped of course but do not have to be shown in ascending order since there is only one booking ref used per charge out sheet. Also, the repeated Booking Ref values will facilitate use of autofilter.

Thanks to all to look at this. I did try to search for a similar thread but did not get anything close.

-Darren
Excel® for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20070) 64-bit

 

[alansidman]

Use Power Query to Unpivot your data and then Group By the Asset and Month. If you would like to see this demo'd then repost your data using XL2BB as we cannot manipulate data in a picture, and there is to much data to try and retype. Help us to help you.

 

[striker]

To be clear, there are no Pivot Tables involved here. Also, the lower 2 screenshots do not contain any formulae or Pivots - there are only what I hope the desired output to look like.
I've used XL2BB to represent the spreadsheet contents visible in the top screenshot (table only since no formulae)

			June															July
		Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
Asset number	Home Office	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
ADV #1	FMM	22-230	22-230	22-230																		22-224	22-224	22-224
ADV #2	FMM																					22-224	22-224	22-224	22-224							22-316
ADV #3	Calgary
ADV #4	NV					22-215	22-215	22-215	22-215	22-215	22-215	22-215	22-467	22-467
ADV #5	Calgary
ADC #1	FMM
ADCP #1	Calgary	22-230	22-230	22-230
Top-set rod #1	FMM	22-230	22-230	22-230																		22-224	22-224	22-224	22-224							22-316
Top-set rod #2	FMM
Top-set rod #3	Calgary
Top-set rod #4	NV					22-215	22-215	22-215	22-215	22-215	22-215	22-215	22-467	22-467
Top-set rod #5	FMM
Winter rod #1	FMM
Winter rod #2	FMM
ADCP Winter Rod	FMM
Level head #1	FMM
Level head #2	FMM	22-230	22-230	22-230																		22-224	22-224	22-224	22-224			22-335	22-335	22-336		22-316
Level head #3	FMM							22-202	22-202	22-202												22-224	22-224	22-224				22-335	22-335	22-336		22-316

 

[alansidman]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filled Up", {{"Column3", type text}}, "en-US"),{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"   June  Asset number", "Asset Number"}, {"   June  Home Office", "Home Office"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Asset Number", "Home Office"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", Text.Trim, type text}, {"Value", Text.Trim, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"Attribute", type date}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Attribute", each Date.MonthName(_), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Month Name",{"Home Office"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Value", "Value - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Value - Copy", "Booking Ref"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[Attribute]), "Attribute", "Value", List.NonNullCount)
in
    #"Pivoted Column"

Asset Number	Booking Ref	June	July
ADCP #1	22-230	3	0
ADV #1	22-224	0	3
ADV #1	22-230	3	0
ADV #2	22-224	0	4
ADV #2	22-316	0	1
ADV #4	22-215	7	0
ADV #4	22-467	2	0
Level head #2	22-224	0	4
Level head #2	22-230	3	0
Level head #2	22-316	0	1
Level head #2	22-335	0	2
Level head #2	22-336	0	1
Level head #3	22-202	3	0
Level head #3	22-224	0	3
Level head #3	22-316	0	1
Level head #3	22-335	0	2
Level head #3	22-336	0	1
Top-set rod #1	22-224	0	4
Top-set rod #1	22-230	3	0
Top-set rod #1	22-316	0	1
Top-set rod #4	22-215	7	0
Top-set rod #4	22-467	2	0

 

[striker]

Wow.. seeing is believing. I've got some learning to do on Power Query. Thanks for the quick answer.