Posts by alansidman
-
-
So if any column has an empty cell, you wish to hide the entire column. Is that what you are asking for?
-
I have added code tags as required in our forum rules. Please re-read these rules that you agreed to abide by when you joined this forum and practice good forum etiquette in the future.
-
A good primer to help you is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. It can be found on Amazon.
-
Display MoreCode
- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Transposed Table" = Table.Transpose(Source),
- #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
- #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
- #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
- #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"SPECIFICATION :"}, "Attribute", "Value"),
- #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
- #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Term"}, {"Attribute.2", "Miles"}, {"Value", "Cost"}}),
- #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Term", Order.Ascending}})
- in
- #"Sorted Rows"
-
I would do it with Power Query. Here is the Mcode and a sample file based upon what you provided.
Display MoreCode- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Demoted Headers" = Table.DemoteHeaders(Source),
- #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
- #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
- #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
- #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
- #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1:"}, "Attribute", "Value"),
- #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
- #"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Attribute.1", Order.Ascending}, {"Column1:", Order.Ascending}})
- in
- #"Sorted Rows"
-
I may be a bit thick on this so would you take your example above and mock up what your solution should look like. You may want to do it in a spreadsheet and attach it to the thread.
-
Please re-read the forum rules you agreed to when you joined. Apparently you have forgotten them. I have added Code tags for you. Please abide by all rules in the future.
-
Our forum rules which you agreed to when you joined are not very onerous. Please reread what you agreed to. I have modified your post to include code tags which you agreed to provide when you joined. Please comply with all forum rules in the future.
-
Your sample workbook does not include Roles that match your request. To assure a workable solution your data must be representable or your actual data and needs. For what it is worth, I would do this with Power Query.
-
Here is a VBA solution for you assuming your data starts in cell A1
Display MoreCode- Option Explicit
- Sub Count3()
- Dim lr As Long
- Dim lc As Long
- Dim x As Long
- lr = Range("A" & Rows.Count).End(xlUp).Row
- lc = Cells(1, Columns.Count).End(xlToLeft).Column
- Dim i As Long, j As Long
- For i = 1 To lr
- x = 0
- For j = 1 To lc
- If Len(Cells(i, j)) > 3 Then
- x = x + 1
- End If
- Next j
- Cells(i, lc + 1) = x
- Next i
- MsgBox "completed"
- End Sub
-
See power pivot solution
-
see file. Created new columns in PQ for PY, Actual, Budget. Built PT in Power Pivot and created Measure to show Budget vs Actual
-
create your PT in Power Pivot and create a measure of Actual vs Budget
-
Here is an alternate solution using Power Query/Get and Transform
Mcode
Code- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"24-Sep", Int64.Type}, {"Column2", type any}, {"Column3", type any}, {"25-Sep", Int64.Type}, {"Column4", type any}, {"Column5", type any}, {"26-Sep", Int64.Type}, {"Column6", type any}, {"Column7", type any}, {"27-Sep", Int64.Type}, {"Column8", type any}, {"Column9", type any}, {"28-Sep", Int64.Type}, {"Column10", type any}, {"Column11", type any}, {"29-Sep", Int64.Type}}),
- #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1", "24-Sep", "25-Sep", "26-Sep", "27-Sep", "28-Sep", "29-Sep"}),
- #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Column1"}, "Attribute", "Value"),
- #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)),
- #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Attribute", "Column1", "Value"})
- in
- #"Reordered Columns"
-
-
Is that a B and C are both blank or Either are Blank. Please clarify.
-
Your post does not comply with our Forum RULES. Use code tags around code.
Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the </> icon at the top of your post window.
(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
-
Instead of importing the file, you can link the csv file and it will automatically update each time you open Access. To link a file,
With Access Open, click on External Data. Select Text file. Follow the wizard to create a link.
-
please use code tags. I have amended your post today. Please read the forum rules you agreed to when you joined this forum.