Posts by spencer264
-
-
Re: Convert/Transpose Values/Content inside one particular column to column heading $
Quote from Ger Plante;700111Hi Spencer- have you paid 10% (2.50) to Ozgrid? I'm going to assume you have.
Code is below and in the attachment ... Note- you had some blanks in your Reason Column, which caused me some headaches, but that aside, this seems to work on your sample data. It might need to be tweaked on your real data, but I made it as generic as possible to allow for that.
The code is commented, but no error handling is put in, as this helps with debugging any issues.
Run Macro "Summarise"
Code
Display MoreOption Explicit Public Sub Summarise() Dim r As Range Dim ws_Temp As Worksheet Dim ws_Solution As Worksheet Dim rItem As Range Dim rItem_List As Range Dim rTarget As Range Dim iCol As Integer Application.DisplayAlerts = False: Application.ScreenUpdating = False Set r = Worksheets("Sheet2").UsedRange 'data to be processed... make sure "Sheet2" is correct. On Error Resume Next: Worksheets("Solution").Delete: On Error GoTo 0 'delete old solution worksheet Set ws_Solution = Worksheets.Add 'create solution worksheet ws_Solution.Name = "Solution" Set ws_Temp = Worksheets.Add 'create a temporart worksheet to play with. r.Columns(6).Replace "", "Blanks", xlWhole 'remove any blank cells in reason codes and replace with word "Blanks" r.Columns(6).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True 'copy unique reasons to a temporary sheet r.Rows(1).Copy Destination:=ws_Solution.Range("A1") 'copy headers to solution header page. ws_Temp.Range("A1").CurrentRegion.Offset(1, 0).Copy 'copy unique reason codes ws_Solution.Range("A1").End(xlToRight).PasteSpecial , , , True 'paste unique reason codes into solution header ws_Temp.UsedRange.Clear 'clear out the temporary sheet. r.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True 'copy unique Item Numbers to a temporary sheet ws_Temp.Rows(1).Delete 'delete header ("Item No.") Set rItem_List = ws_Temp.Range("A1").CurrentRegion 'set up unique list of items. For Each rItem In rItem_List r.AutoFilter 1, rItem 'process each item.... Set rTarget = r.Offset(1, 0).SpecialCells(xlCellTypeVisible) rTarget.Cells(1, 1).Resize(, 5).Copy Destination:=ws_Solution.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 'use SUMIFS formula to fill in the matrix of reason codes For iCol = 6 To ws_Solution.Cells(1, 1).End(xlToRight).Column ws_Solution.Range("A" & Rows.Count).End(xlUp).Offset(, iCol - 1) = Application.WorksheetFunction.SumIfs(r.Columns(4), r.Columns(1), rTarget.Cells(1, 1), r.Columns(6), ws_Solution.Cells(1, iCol).Value) Next Next 'clean up r.AutoFilter 'turn off autofilter ws_Solution.Cells.Replace "Blanks", "", xlWhole 'remove "Blanks" from reason code headers. r.Replace "Blanks", "", xlWhole 'remove "Blanks" from reason code list. ws_Solution.Cells.Replace 0, "", xlWhole 'remove "zeros" from reason code matrix. ws_Solution.Columns(4).Delete 'remove qty column from solution tab (not needed) ws_Solution.Columns.AutoFit ws_Temp.Delete 'delete temporary sheet. Application.Goto ws_Solution.Range("A1"), True Application.DisplayAlerts = True: Application.ScreenUpdating = True End Sub
spencer264
[INDENT]run time error 1004? and debug shows as:
r.Columns(6).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True 'copy unique reasons to a temporary sheet
Yes 10% paid to Ozgrid and the remaining pay to Ozgrid as well?
Is it because I apply the Macro the actual files which has more than 10,000 rows?
Thanks[/INDENT] -
Re: Convert/Transpose Values/Content inside one particular column to column heading $
run time error 1004? and debug shows as:
r.Columns(6).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True 'copy unique reasons to a temporary sheet
Yes 10% paid to Ozgrid and the remaining pay to Ozgrid as well?
Is it because I apply the Macro the actual files which has more than 10,000 rows?
Thanks -
-
The sample file is attached.
The yellow highlighted column F (each Reason) in the sheet2 to be placed as main header in the next column and so on as seen in the sheet 3
If they have two similar item number with 2 different reasons or more - delete the duplicate item number and place the number in each column according to its reason as seen on row 16 on the sheet3
If they have two number with similar reason - just sum up the number
Thank you
-
The sample file is attached.
The yellow highlighted column F (each Reason) in the sheet2 to be placed as main header in the next column and so on as seen in the sheet 3
If they have two similar item number with 2 different reasons - delete the duplicate item number and place the number in each column according to its reason as seen on row 16 on the sheet3
If they have two number with similar reason - just sum up the number
Thank youforum.ozgrid.com/index.php?attachment/58634/ -
Re: move several columns and sort according to the one particular column
Quote from smuzoen;699838And if there is no match the row is moved to its own row on sheet 3? If no match of data on Sheet 1 then copy to left side of Sheet 3 and if no data match on Sheet 2 then move to Right side of Sheet3? Is that correct?
Yes it does..
It really works awesome Thank you very much
-
The file is attached. I need to move the content on the sheet 2 and if the item number on the sheet 2 is matched with the one in sheet one move them in the same row just like in the sheet 3 and the same things to the sheet one. The point is to make the same item number between sheet 1 and sheet 2 in the same row
Thank you