Posts by alansidman

alansidman · Nov 22nd 2020

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

alansidman · Nov 19th 2020

crossposted: https://www.mrexcel.com/board/…sheet-name-daily.1151450/

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

alansidman · Nov 11th 2020

Are the latest 15 anywhere in the list or are they the last 15 items in a column?

alansidman · Nov 1st 2020

Reported other locations

Cross posted

mrexcel.com/board/threads/conv…h-a-dropdown-box.1148769/

excelguru.ca/forums/showthread…a-dropdown-box-(hardcoded)

vbaexpress.com/forum/showthrea…a-dropdown-box-(hardcoded)

alansidman · Oct 31st 2020

It is apparent that you did not bother to read the forum rules that you agreed to when you joined. Please take a few minutes to re=read them and comply in the future. You have crossposted to at least two other sites and have not bothered to use code tags. I have corrected this in your post. Please comply in the future.

alansidman · Oct 31st 2020

Quicker and easier in PQ, but here is some code for you.

Code

Option Explicit
Sub PQSubstitute()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim lr As Long, lr2 As Long
Dim i As Long
Application.ScreenUpdating = False
lr = s1.Range("A" & Rows.Count).End(xlUp).Row
s1.Range("A1:E1").Copy s2.Range("B1")
For i = 2 To lr
lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
s1.Range("F1:P1").Copy
s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues, , , True
s1.Range("A" & i & ":E" & i).Copy
s2.Range("B" & lr2 + 1).PasteSpecial xlPasteValues
s1.Range("F" & i & ":P" & i).Copy
s2.Range("G" & lr2 + 1).PasteSpecial xlPasteValues, , , True
Next i
lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
For i = 3 To lr
If s2.Range("B" & i) = "" Then
s2.Range("B" & i - 1 & ":F" & i - 1).Copy s2.Range("B" & i)
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "complete"
End Sub

Display More

alansidman · Oct 29th 2020

I changed one line of your sample to IN TRANSIT. I then amended the code as follows. Added a new column that said, if Delivery Date is "IN TRANSIT" then change date to 1/1/1900 which allows you to then flag those deliveries.

Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "New Delivery", each if [delivery date]= "IN TRANSIT" then 1/1/1900 else [delivery date]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"New Delivery", type date}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"delivery date"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"shipping out date", "Month", "New Delivery", "Ref", "PO Number"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "PO Number", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), {"PO Number.1", "PO Number.2", "PO Number.3", "PO Number.4", "PO Number.5", "PO Number.6", "PO Number.7", "PO Number.8", "PO Number.9", "PO Number.10", "PO Number.11", "PO Number.12", "PO Number.13", "PO Number.14", "PO Number.15", "PO Number.16", "PO Number.17", "PO Number.18", "PO Number.19", "PO Number.20", "PO Number.21"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"shipping out date", "New Delivery", "Month", "Ref"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "PO"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"shipping out date", type date}})
in
#"Changed Type"

Display More

alansidman · Oct 29th 2020

What would like to happen if the value is "IN Transit?" Put an artificial date? If yes, then what?

alansidman · Oct 29th 2020

Using Power Query/Get and Transform, I have put the data into a normalized table. Here is the Mcode for that action. The file is attached for your review.

Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"shipping out date", type date}, {"delivery date", type date}, {"Month", type text}, {"Ref", Int64.Type}, {"PO Number", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "PO Number", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), {"PO Number.1", "PO Number.2", "PO Number.3", "PO Number.4", "PO Number.5", "PO Number.6", "PO Number.7", "PO Number.8", "PO Number.9", "PO Number.10", "PO Number.11", "PO Number.12", "PO Number.13", "PO Number.14", "PO Number.15", "PO Number.16", "PO Number.17", "PO Number.18", "PO Number.19", "PO Number.20", "PO Number.21"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"shipping out date", "delivery date", "Month", "Ref"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "PO"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
#"Removed Columns"

alansidman · Oct 27th 2020

Please re-read the forum rules you agreed to when you joined. You have not provided code tags for your VBA code as prescribed. I have today provided those for you. In the future please abide by all forum rules.

alansidman · Oct 26th 2020

Please read the forum rules you agreed to when you joined. Pay particular attention to the one regarding Code Tags. I have added them for you today. Please do so in the future.

alansidman · Oct 21st 2020

Alternative solution with Power Query

Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Inserted Prefix" = Table.AddColumn(Source, "Prefix", each "000" & Text.From([Column12], "en-US"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Prefix",{"Column12"})
in
#"Removed Columns"

alansidman · Oct 21st 2020

crossposted: mrexcel.com/board/threads/exce…ress-requirement.1148912/

alansidman · Oct 21st 2020

crossposted: mrexcel.com/board/threads/copy…another-workbook.1148959/

alansidman · Oct 20th 2020

Since you didn't show a solution for the benefit of others, see attached.

alansidman · Oct 10th 2020

Attached is a Parameter Query created in Power Query.

alansidman · Oct 9th 2020

Suggest you upload a sample worksheet with approximately 8-10 records so that we can develop a solution for you.

alansidman · Oct 8th 2020

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.

alansidman · Sep 17th 2020

I have amended your post for you to include Code Tags. These are required when you post code as outlined in the forum rules you agreed to when you joined. Please take a few minutes to re read the forum rules and abide by them in future postings.

alansidman · Sep 12th 2020

The following code will identify any number in Column A beginning with 2 and paste it into column F

Code

Option Explicit
Sub Filter2()
Dim i As Long, lr As Long, lr2 As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr
If InStr(Range("A" & i), 2) = 1 Then
lr2 = Range("F" & Rows.Count).End(xlUp).Row
Range("F" & lr2 + 1) = Range("A" & i)
End If
Next i
End Sub

Display More

Posts by alansidman

How To Copy All Files From One Folder To Another Based On A List of Unique Identification Number (UIN) listing in Excel ?

Capture data from a web workbook that changes the sheet name daily

dynamic list length to find top 8 from last 20

Converting three calculators in a workbook into one with a dropdown box (hardcoded)

Macro Recorder to insert Pivot Table .. gives Runtime 5 error after recording it and running Macro

VBA to unpivot a Fixed number of columns just like Power QUery

Vlook up

Vlook up

Vlook up

Debugging VBA built on Excel2013 not working on v2016

Sumif - User defined Function

Adding specified text at the beginning of each cells in a specific column

Splitting up of Address

Copy Data from one workbook to another workbook with email notification

Separating cell info counting in a pivot table

Sort and filter 3 columns

Sort and filter 3 columns

Use VBA to filter column A of main table on WS1, paste into WS2, repeat 10 times

toplama

Autofilter in the column with the first character