Posts by AlanSidman
-
-
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
-
1. I have no idea what you are asking. Your explanation is confusing and not clear.
2. We cannot manipulate data in a picture
3. Suggest you upload a sample file.
4. Using the sample file data you upload, also mock up and upload a solution of what you want your result to look like.
5. You may wish to explain once again the logic you use in the samples you upload
-
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 result in the file
and the Mcode that made it happen
Code- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", Int64.Type}, {"D", type text}}),
- #"Grouped Rows" = Table.Group(#"Changed Type", {"A", "B", "D"}, {{"Total", each List.Sum([C]), type number}})
- in
- #"Grouped Rows"
-
Did you bother to test it to see if it does what you want instead of guessing that it does or does not do what you want?
-
-
Using Power Query/Get and Transform, here is the Mcode generated by that action.
Display MoreCode- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- #"Split Column by Delimiter" = Table.SplitColumn(Source, "Reported Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Reported Date.1", "Reported Date.2"}),
- #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Reported Date.1", type text}, {"Reported Date.2", type time}}),
- #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Actual Finish", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Actual Finish.1", "Actual Finish.2"}),
- #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Reported Date.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Reported Date.1.1", "Reported Date.1.2", "Reported Date.1.3"}),
- #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Reported Date.1.1", Int64.Type}, {"Reported Date.1.2", Int64.Type}, {"Reported Date.1.3", Int64.Type}, {"Actual Finish.1", type text}, {"Actual Finish.2", type time}}),
- #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type1", "Actual Finish.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Actual Finish.1.1", "Actual Finish.1.2", "Actual Finish.1.3"}),
- #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Actual Finish.1.1", Int64.Type}, {"Actual Finish.1.2", Int64.Type}, {"Actual Finish.1.3", Int64.Type}}),
- #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Reported Date.1.2", type text}, {"Reported Date.1.1", type text}, {"Reported Date.1.3", type text}}, "en-US"),{"Reported Date.1.2", "Reported Date.1.1", "Reported Date.1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Reported Date"),
- #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Reported Date.2", type text}}, "en-US"),{"Reported Date", "Reported Date.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Reported DateTime"),
- #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Actual Finish.1.2", type text}, {"Actual Finish.1.1", type text}, {"Actual Finish.1.3", type text}}, "en-US"),{"Actual Finish.1.2", "Actual Finish.1.1", "Actual Finish.1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
- #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns2",{{"Merged", type date}}),
- #"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type3", {{"Merged", type text}, {"Actual Finish.2", type text}}, "en-US"),{"Merged", "Actual Finish.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Actual Fininsh DateTime"),
- #"Changed Type4" = Table.TransformColumnTypes(#"Merged Columns3",{{"Reported DateTime", type datetime}, {"Actual Fininsh DateTime", type datetime}}),
- #"Added Custom" = Table.AddColumn(#"Changed Type4", "Duration", each [Actual Fininsh DateTime]-[Reported DateTime]),
- #"Inserted Days" = Table.AddColumn(#"Added Custom", "Days", each Duration.Days([Duration]), Int64.Type),
- #"Inserted Hours" = Table.AddColumn(#"Inserted Days", "Hours", each Duration.Hours([Duration]), Int64.Type),
- #"Inserted Minutes" = Table.AddColumn(#"Inserted Hours", "Minutes", each Duration.Minutes([Duration]), Int64.Type),
- #"Removed Columns" = Table.RemoveColumns(#"Inserted Minutes",{"Duration"})
- in
- #"Removed Columns"
Display MoreCode- Review PQ
- In the attached file
- Click on any cell in the new table
- On the Data Tab, click on Queries & Connections
- In the right window, double click to open Query
- Review PQ steps
- M-code basics:
- - "let" is the start of a query
- - "in" is the closing of a query
- - each transformation step sits in between those 2 lines
- - each step line is ended by a comma, except the last one
- - "Source" is always the first step (Source Data)
- - After "in" you have the last step referenced
-
1. Highlight row 1. Click on Data Tab. Click on Filter
2. Click on Drop down for "Does not Meet." Uncheck all boxes except 1.
3. Click on Drop down for "On Hold." Uncheck the boxes with 1.
4. You can now copy and paste remaining data to another worksheet for analysis.
-
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
crosspost: https://www.excelforum.com/exc…ithin-the-same-sheet.html
-
crossposted: https://www.excelforum.com/exc…k-menu-item-in-macro.html
https://chandoo.org/forum/thre…menu-item-in-macro.44402/
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
-
-
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
-
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.)
-
I think the issue here was you cannot edit your post after a period of time. In such a case, put it in the next post available in the thread. No problem, I added the actual thread in both forums. But now you know. With the link to the actual thread others can see if there is any progress before venturing into the issue.
-
-
-
Cross-posting Without Telling Us
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.
Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!
Read https://www.excelguru.ca/content.php?184 to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
I have provided the crosspost today. Please observe the forum rules you agreed to when you joined.
Crosspost: https://www.mrexcel.com/board/…-access-database.1134610/
-
Here is a video on how to do that
-
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.)
