Want to rename columns, delete unwanted columns, and reconcile reports

Topic

New Reply

Greetings,

Given the attach sample workbook with order and receiving report, I would like to:

1. Rename column names that are labeled differently in receiving report to match those in order report

2. Delete the remaining columns

I’ll then use vlookup, macro, or formulas to reconcile the two reports.

What would be the best tool (Access or Excel) to automate this process?

TIA,

Regards,

Reply | Quote

Replies

Since you are in excel, I think excel. How about some code like this (test on a copy)

Code:

Option Explicit
Sub RenDelCols()
  Dim vCols As Variant
  Dim vNames As Variant
  Dim iCols As Integer
  Dim iCol As Integer
  Dim wks As Worksheet
  Dim i As Integer
  
  'define the worksheet
  Set wks = Worksheets("Receiving")
  'Cols in Receiving to be renamed
  vCols = Array(1, 12, 14, 17, 24, 25) 'Cols A,L,N,Q,X,Y
  'Names from Order to rename them to
  vNames = Array("Order Date", "Order Number", "Order Type", "Supplier", "Total", "Account Code")
  
  With wks
    iCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For iCol = iCols To 1 Step -1
      i = 0
      'check if col number is one to change
      On Error Resume Next
      i = Application.WorksheetFunction.Match(iCol, vCols, 0)
      On Error GoTo 0
      If i = 0 Then
        'column is not in list, delete it
        .Columns(iCol).EntireColumn.Delete
      Else
        'col is in list, rename it
        .Cells(1, iCol).Value = vNames(i - 1)
      End If
    Next
  End With
End Sub

Steve

Reply | Quote

Thanks Steve,

It worked nicely for my second report (Receiving Report): renamed needed columns, and deleted unwanted columns.
However, I would also like to delete unwanted columns from my first report (Order report). With the exception of the following:

Order Number (A]
Order Date (E)
Order Type (F)
Blanket Order (H)
Supplier (L)
Total (W)
Account Code (X)

Regards,

Reply | Quote

You should be able to adapt the code above. Change the worksheet name, change columns numbers in the array and delete the lines of code that rename the columns headings.

Steve

Reply | Quote

Steve, thanks and I have the needed columns in both my reports (order and receiving) that are in the same workbook. What would be the best way to load them into one report for reconciliation purposes?

Regards,

Reply | Quote

You can create a new sheet and copy the columns into that one or copy one set into the other.

If you are unsure of the code, I would recommend do the task with the macro recorder to see the objects.

Steve

Reply | Quote