Rubin Shrestha
Rubin Shrestha
Blog

How to transform data into from a report to be workable? [closed]

by |Published

So I was tasked to find a way to work with a report that was given me data this way (pictured below).

Example of Source Data

I wanted to make it workable so that it will generate a simple list that would be workable.
My Final resul is for a list that:

Model Code: / Site / OH qty for the Site.

i was able to create a macro for it (power query doesnt work on our computers for some reason :/)

Is there a way for this to be streamlined?

Application.ScreenUpdating = False
'Input helper & filters
    Sheets(" InStock").Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    '.End(xlUp).Offset(1).Row
    'lastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Helper"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-3]<100%,OR(RC[-2]=""A"",RC[-2]=""B"")),""Keep"",IF(AND(RC[-3]<100%,RC[-2]=""C"",RC[-1]=""OTIF - 48""),""Keep"",""Skip""))"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M" & lastRow)
    Range("M2").Select
    
    Rows("1:1").Select
    ActiveSheet.Range("$A$1:$M$" & lastRow).AutoFilter Field:=13, Criteria1:="Keep"


'Copy items

    'Sheets(" InStock").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
   
    Sheets("Sorted").Select
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    
    Range("A2").Select
    ActiveSheet.Paste
    
 'copy 1st location
    Sheets(" InStock").Select
    Range("B1").Select
    Selection.Copy
    
    Sheets("Sorted").Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
     
     Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:B" & lastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    

  'copy items again for 2nd location
    Sheets(" InStock").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Sheets("Sorted").Select
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    
    Range("A" & AlastRow).Select
    ActiveSheet.Paste
    
  'Copy location 2
    Sheets(" InStock").Select
    Range("C1").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Sheets("Sorted").Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    Range("B" & Blastrow & ":B" & lastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
 'copy items again for 3nd location
    Sheets(" InStock").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Sheets("Sorted").Select
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    
    Range("A" & AlastRow).Select
    ActiveSheet.Paste
    
  'Copy location 3
    Sheets(" InStock").Select
    Range("D1").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Sheets("Sorted").Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    Range("B" & Blastrow & ":B" & lastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'copy items again for 4nd location
    Sheets(" InStock").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Sheets("Sorted").Select
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    
    Range("A" & AlastRow).Select
    ActiveSheet.Paste
    
  'Copy location 4
    Sheets(" InStock").Select
    Range("E1").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Sheets("Sorted").Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    Range("B" & Blastrow & ":B" & lastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
  'copy items again for 5nd location
    Sheets(" InStock").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Sheets("Sorted").Select
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    
    Range("A" & AlastRow).Select
    ActiveSheet.Paste
    
  'Copy location 5
    Sheets(" InStock").Select
    Range("F1").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Sheets("Sorted").Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    Range("B" & Blastrow & ":B" & lastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
'copy items again for 6nd location
    Sheets(" InStock").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Sheets("Sorted").Select
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    
    Range("A" & AlastRow).Select
    ActiveSheet.Paste
    
  'Copy location 6
    Sheets(" InStock").Select
    Range("G1").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Sheets("Sorted").Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    Range("B" & Blastrow & ":B" & lastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'copy items again for 7nd location
    Sheets(" InStock").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Sheets("Sorted").Select
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    
    Range("A" & AlastRow).Select
    ActiveSheet.Paste
    
  'Copy location 7
    Sheets(" InStock").Select
    Range("H1").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Sheets("Sorted").Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    AlastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Blastrow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
    Range("B" & Blastrow & ":B" & lastRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(@C1,' InStock'!C1:C8,MATCH(Sorted!RC[-1],' InStock'!R1C1:R1C8,0),0),0)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & lastRow)
    Range("C2:C" & lastRow).Select
    Range("D1").Select
    
    Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]&"" ""&VLOOKUP(RC[-2],keys!C[-3]:C[-2],2,0)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC[-1],FesVsSap!C[-4],1,0),""Next"")=RC[-1],""R+D AM"",IF(VLOOKUP(@C1,' InStock'!C1:C8,MATCH(Sorted!RC[-3],' InStock'!R1C1:R1C8,0),0)>0,""-"",IF(XLOOKUP(RC[-1]&"" AVAIL. - OH"",'DF Data'!C[16],'DF Data'!C,""Pull DF Data"")=0,""0 OH"",IF(XLOOKUP(RC[-1]&"" AVAIL. - OH"",'DF Data'!C[16],'DF Data'!C,""Pull DF Data"")=""Pull DF Data""," & _
        """Pull DF Data"",""Needs Recovery""))))" & _
        ""
    Range("D2:E2").Select
    Selection.Copy
    Range("D3:E" & lastRow).Select
    ActiveSheet.Paste
    
    Range("F1").Select
    
  Application.ScreenUpdating = True

You may also like

Introducing PHP-Watcher
Published

Introducing PHP-Watcher

A package to automatically restart PHP application once the source code changes. Can be very useful for developing long-running PHP applications