=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)
Posts by AlanSidman
-
-
An alternative solution is to use Power Query. Load your range to PQ. Highlight all columns and click on Unpivot. The Mcode follows
Code- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
- #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
- #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
- in
- #"Removed Columns"
-
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.)
-
-
Read this link to understand the philosophy of cross posting requirements.
-
If you are using Excel 2016, then converting the format in Power Query is a quick and simple step.
-
Not sure which dates need to be converted. Suggest you provide more detailed information about your worksheet and what you want to happen. Your explanation and worksheet are not clear. We are not mind readers here, but we are excellent excel helpers.
-
Tried a different method
Display MoreCode- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
- #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
- #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type time}, {"Date.3", type text}}),
- #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Date.1", type text}}, "en-US"), "Date.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1.1", "Date.1.2", "Date.1.3"}),
- #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Date.1.1", Int64.Type}, {"Date.1.2", Int64.Type}, {"Date.1.3", Int64.Type}}),
- #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Date.1.2", "Date.1.1", "Date.1.3", "Date.2", "Date.3"}),
- #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Date.1.2", type text}, {"Date.1.1", type text}, {"Date.1.3", type text}}, "en-US"),{"Date.1.2", "Date.1.1", "Date.1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"NewDate"),
- #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Date.2", "Date.3"})
- in
- #"Removed Columns"
Left out step using Locale. See if that works for you.
-
Code Tags Added
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.)
-
crossposted: https://www.excelforum.com/off…hs-and-12-months-ago.html
Your post does not comply with our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.
Post a link to any other forums where you have asked the same question.
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.
Go to https://www.excelguru.ca/content.php?184 to understand why we ask you to do this.
I have added the crosspost reference for you today. Please comply with this and all our rules in the future
-
The issue is probably the locale setting in your defaults. Mine is US. If yours is something else, you will need to change it or change your Mcode to reflect your locale.
-
Code Tags Added
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.)
-
-
Using Power Query/Get and Transform, here is the Mcode for solving your issue
Code- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
- #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-US"),
- #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type with Locale", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
- #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}, {"Date.3", Int64.Type}}),
- #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date.2", "Date.1", "Date.3"}),
- #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Date.2", type text}, {"Date.1", type text}, {"Date.3", type text}}, "en-US"),{"Date.2", "Date.1", "Date.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged")
- in
- #"Merged Columns"
-
Instead of trying to reverse engineer your formula, suggest you explain in plain English what you want to happen. Additionally, you have shown us what you have. Now, mock up a solution so that we have a clear idea of what you want to happen.
-
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired results are also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
-
Quote
I can’t make it working
I cannot help you any further.
-
-
Here is the new Mcode
Code- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type number}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", type number}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", Int64.Type}, {"Column21", type number}, {"Column22", Int64.Type}, {"Column23", type number}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", type number}, {"Column28", type number}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", type number}, {"Column32", Int64.Type}, {"Column33", Int64.Type}, {"Column34", Int64.Type}, {"Column35", Int64.Type}, {"Column36", Int64.Type}, {"Column37", type number}, {"Column38", type number}, {"Column39", Int64.Type}, {"Column40", Int64.Type}, {"Column41", type number}, {"Column42", Int64.Type}, {"Column43", type number}, {"Column44", Int64.Type}, {"Column45", Int64.Type}, {"Column46", Int64.Type}, {"Column47", type number}, {"Column48", type number}, {"Column49", type number}, {"Column50", Int64.Type}, {"Column51", type number}, {"Column52", Int64.Type}, {"Column53", type number}, {"Column54", Int64.Type}, {"Column55", Int64.Type}, {"Column56", Int64.Type}, {"Column57", type number}, {"Column58", type number}, {"Column59", type number}, {"Column60", Int64.Type}, {"Column61", type number}}),
- #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null and [Column1] <> "subtotal")),
- #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Column1"}, "Attribute", "Value"),
- #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)),
- #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute"})
- in
- #"Removed Columns"
Code- If you have multiple sheets that need to be merged into the solution, then you can add each to PQ, Append each to the other and then run your query (Mcode). This is a quick and easy reformatting that you are requesting. Do a bit of research on Power Query and you may be surprised at what you can accomplish.
-
Here is a VBA solution. I do not have a Formula solution for you.
Display MoreCode- Option Explicit
- Sub Absent()
- Dim lrB As Long, lrC As Long, lrH As Long
- Dim i As Long, j As Long
- lrB = Range("B" & Rows.Count).End(xlUp).Row
- lrC = Range("C" & Rows.Count).End(xlUp).Row
- Application.ScreenUpdating = False
- For i = 4 To lrB
- Dim res As Variant
- On Error Resume Next
- Err.Clear
- res = Application.WorksheetFunction.VLookup(Range("B" & i), Range("C4:C" & lrC), 1, False)
- If Err.Number <> 0 Then
- lrH = Range("H" & Rows.Count).End(xlUp).Row + 1
- Range("H" & lrH) = Range("B" & i)
- End If
- Next i
- Application.ScreenUpdating = True
- MsgBox "Completed"
- End Sub