Do not post a new question in an existing thread. This is called highjacking. Open a new thread and if necessary reference this one. I am closing this thread to further posts.
Posts by alansidman
-
-
-
-
-
Please use code tags when posting any type of codes. I have added them for you today. Please read the rules you agreed to when you joined this forum.
-
give this a try
Code
Display MoreOption Explicit Sub dazz() Dim i As Long, lr As Long Dim IB As String Application.ScreenUpdating = False IB = Application.InputBox(prompt:="What word to look up?") lr = Range("M" & Rows.Count).End(xlUp).Row For i = lr To 2 Step -1 If InStr(Range("M" & i), IB) > 0 Then Range("M" & i).EntireRow.Delete End If Next i Application.ScreenUpdating = True MsgBox "Completed" End Sub
-
Supply us with a sample worksheet. 10-15 Records showing a before and after presentation that is representative of your actual file. Upload a file and do not provide a picture as we cannot manipulate data in a picture.
-
This is a duplicate post of Adding numbers
Please do not create duplicate posts. I am closing this thread. You may continue in the original.
-
code to delete empty rows.
Code
Display MoreOption Explicit Sub DeleteBlankRows3() 'Deletes the entire row within the selection if _ the ENTIRE row contains no data. Dim LastRow As Integer Dim MyRow As Integer Application.ScreenUpdating = False LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count For MyRow = LastRow To 1 Step -1 If Application.CountA(Rows(MyRow)) = 0 Then Rows(MyRow).Delete Next MyRow Application.ScreenUpdating = True End Sub
Here is code for deleting empty columns
Code
Display MoreOption Explicit Sub DelCol() Dim j As Long Dim lc As Long, lr As Long Application.ScreenUpdating = False lc = Cells(1, Columns.Count).End(xlToLeft).Column For j = lc To 1 Step -1 lr = Cells(Rows.Count, j).End(xlUp).Row If WorksheetFunction.CountA(Range(Cells(1, j), Cells(lr, j))) = 0 Then Cells(1, j).EntireColumn.Delete End If Next j Application.ScreenUpdating = True End Sub
-
Data-->Text to Columns. Split with a delimiter of "@" It will split to two columns
Otherwise use Power Query (Get and Transform Data). Get Data from Range/Table. Select Split Column. Select your delimiter and then select Split to Rows.
-
You refer to the 120 contiguous cells. Which ones are to be evaluated. I am not understanding your needs. Please expand your explanation referencing the worksheet you have attached.
-
See the Power Query Solution attached.
Load your tables to PQ Editor and Join on Common fields. Then pivot the data and combine the pivoted columns with commas.
-
Please do not highjack an existing thread. You should start a new thread so as to not cause any confusion. If you feel that the information in this thread is relevant then reference it in your new thread. I am closing this thread so that you may start a new one.
-
-
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided. -
I am not sure what you meant by this line
Quotein column "H" add the number in that meet this criteria of like ID Codes.
I used Power Query to solve as shown below. If this is not what you are looking for, then supply us with a sample mocked up solution for the sample provided.
Here is the Mcode for the Power Query
Codelet Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Filtered Rows" = Table.SelectRows(Source, each ([name] = "test1")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"id code"}, {{"Count", each List.Sum([#"#of BFF"]), type number}, {"Total", each List.Sum([Settled]), type number}}) in #"Grouped Rows"
id code Count Total 123-19-001 3 1027640 110-11-002 7 225064.4 508-11-002 1 177234 135-11-002 1 355541 243-11-002 11 276005 562-11-006 18 280494 593-11-006 2 0 588-12-001 1 -165599 521-11-002 1 158455 551-51-002 1 178692 512-11-008 9 288026 243-12-002 8 256306 631-10-001 3 319676 551-51-001 1 160741 251-11-001 8 1730683 123-41-010 2 631261 562-11-001 1 170142 631-41-001 25 2197068 251-12-001 6 1728795 123-16-001 5 234512.48 120-11-008 1 200257 562-11-005 16 280624 150-11-001 7 430202 521-21-001 7 228779 634-21-001 2 213010 529-11-004 2 227592 256-20-001 1 0 254-20-004 1 279628 588-11-003 5 388900 -
-
Here is how I would do it.
1. Group the table on the Invoice Line and sum the two tax columns
Codelet Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Date", type date}, {"Tax Collected", type number}, {"Tax Rate", Percentage.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice Line"}, {{"Sum of Tax Collected", each List.Sum([Tax Collected]), type nullable number}, {"Sum of Tax Rate", each List.Sum([Tax Rate]), type nullable number}}) in #"Grouped Rows"
Then Merge (join) the new query with the original data (ie. Merge back on itself)
Codelet Source = Table.NestedJoin(Table1, {"Invoice Line"}, #"Table1 (2)", {"Invoice Line"}, "Table1 (2)", JoinKind.LeftOuter), #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Co Code", "Billing Party", "Invoice Number", "Invoice Date", "Customer Class", "Customer Number", "Customer Name", "TPC", "TPC Name", "Line Description", "Prod Type", "Item Name", "Tax Category", "Distr Method", "DesignLive", "GeoTool", "OrderForm", "RptUsage", "Tax Result", "Exempt Code", "OBM Brand", "Product Owner", "SepStatedCat", "PerformLoc", "Sales Person", "Linked Invoice", "UseTax Reason", "SourceMethod", "ClientBill State", "ClientHQ State", "ShipTo Zip", "ZipSellPrice", "ZipQuantity", "ShipTo State", "ShipTo County", "ShipTo City", "Tax Base Amount", "ShipFrom Plant", "ShipFrom State", "ShipFrom Zip", "Interim Plant", "Osprey CustNo", "Osprey OrderID", "Osprey MailDropID"}, {"Table1 (2).Co Code", "Table1 (2).Billing Party", "Table1 (2).Invoice Number", "Table1 (2).Invoice Date", "Table1 (2).Customer Class", "Table1 (2).Customer Number", "Table1 (2).Customer Name", "Table1 (2).TPC", "Table1 (2).TPC Name", "Table1 (2).Line Description", "Table1 (2).Prod Type", "Table1 (2).Item Name", "Table1 (2).Tax Category", "Table1 (2).Distr Method", "Table1 (2).DesignLive", "Table1 (2).GeoTool", "Table1 (2).OrderForm", "Table1 (2).RptUsage", "Table1 (2).Tax Result", "Table1 (2).Exempt Code", "Table1 (2).OBM Brand", "Table1 (2).Product Owner", "Table1 (2).SepStatedCat", "Table1 (2).PerformLoc", "Table1 (2).Sales Person", "Table1 (2).Linked Invoice", "Table1 (2).UseTax Reason", "Table1 (2).SourceMethod", "Table1 (2).ClientBill State", "Table1 (2).ClientHQ State", "Table1 (2).ShipTo Zip", "Table1 (2).ZipSellPrice", "Table1 (2).ZipQuantity", "Table1 (2).ShipTo State", "Table1 (2).ShipTo County", "Table1 (2).ShipTo City", "Table1 (2).Tax Base Amount", "Table1 (2).ShipFrom Plant", "Table1 (2).ShipFrom State", "Table1 (2).ShipFrom Zip", "Table1 (2).Interim Plant", "Table1 (2).Osprey CustNo", "Table1 (2).Osprey OrderID", "Table1 (2).Osprey MailDropID"}), #"Removed Duplicates" = Table.Distinct(#"Expanded Table1 (2)", {"Invoice Line"}) in #"Removed Duplicates"
-
Have been travelling for the past two weeks with no laptop. Will look this over this weekend.
Alan
-
Be sure that macro is embedded in the correct file. Ensure that macros are enabled on all computers.