Match column headers and copy over data

suptechguy

New Member
Joined
Jan 15, 2020
Messages
5
Office Version
365
Platform
Windows
I am starting a new project to simplify an uploading process. I would like to create a workbook that is set up like this:

Two sheets, one is called “Upload Data”, and the other is called “Prepared File. The “Prepared File” sheet has the column headers in the correct order for the upload to work properly. When users copy and paste data into the “Upload Data” sheet, then the data for each column will copy into sheet “Prepared File’ and will be arranged in the correct order.



I think the logic would look something like this:

In sheet (“Prepared File”) in range (“9:9”)

Match each column header (A9, B9, C9, …) with the column headers in sheet (“Upload data”) in range (“A:A”).

If exact match is found, copy the data in that column from Sheet (“Upload Data”) into the matching column on sheet (“Prepared File”)

If a match is not found, then put a (“x”) above that column (A8, B8, C8, …) in sheet (“Prepared File”)



Please help. Thank!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,216
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Are you looking for a macro or a formula approach?
 

suptechguy

New Member
Joined
Jan 15, 2020
Messages
5
Office Version
365
Platform
Windows
So basically, each column header in row (10:10) of sheet ("Prepared File") needs to find the matching column header in row (1:1) of sheet ("Upload Data")
If a match is found, copy the data in that column of sheet ("Upload Data") into the matching column of sheet ("Prepared File")
'If there is no match, place an 'x' above the column header in sheet ("Prepared File") to show that there was no match

I think VBA will be the best choice now
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,216
Office Version
365
Platform
Windows
I think VBA will be the best choice now
Give this a try in a copy of your workbook.

VBA Code:
Sub Copy_Columns()
  Dim wsUD As Worksheet, wsPF As Worksheet
  Dim rCell As Range, rFound As Range
  
  Set wsUD = Sheets("Upload Data")
  Set wsPF = Sheets("Prepared File")
  With wsPF
    For Each rCell In .Range("A10", .Range("A10").End(xlToRight))
      Set rFound = wsUD.Rows(1).Find(What:=rCell.Value, LookAt:=xlWhole, MatchCase:=False)
      If rFound Is Nothing Then
        rCell.Offset(-1).Value = "x"
      Else
        wsUD.Range(wsUD.Cells(2, rFound.Column), wsUD.Cells(Rows.Count, rFound.Column).End(xlUp).Offset(1)).Copy Destination:=rCell.Offset(1)
      End If
    Next rCell
  End With
End Sub
My sample data

xl2bb.xlam
ABC
1GuyOtherSuper
2Guy 1Other 1Super 1
3Guy 2Other 2Super 2
4Guy 3Other 3Super 3
5Guy 4Other 4Super 4
6Guy 5Super 5
7Guy 6Super 6
8Guy 7
9
Upload Data



Results of code when this sheet originally just had the green cells.

xl2bb.xlam
ABC
9x
10SuperTechGuy
11Super 1Guy 1
12Super 2Guy 2
13Super 3Guy 3
14Super 4Guy 4
15Super 5Guy 5
16Super 6Guy 6
17Guy 7
18
Prepared File
 

suptechguy

New Member
Joined
Jan 15, 2020
Messages
5
Office Version
365
Platform
Windows
This is brilliant thank you! What if I want an "x" to also appear above the column headers of sheet ("Upload Data"). That way, users can see which columns they need to fix on the Upload Tab. When they fix the columns and press the button again, the columns with the x's should reset. Can this be adjusted?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,216
Office Version
365
Platform
Windows
What if I want an "x" to also appear above the column headers of sheet ("Upload Data").
In my example do you mean mark the 'Other' column in 'Upload Data'?

If so try adding these two blue lines where shown.

Rich (BB code):
  Set wsPF = Sheets("Prepared File")
  wsUD.Range("A1", wsUD.Cells(1, Columns.Count).End(xlToLeft)).Interior.Color = vbYellow
  With wsPF
.
.
      Else
        rFound.Interior.Color = xlNone
        wsUD.Range(wsUD.Cells(2, rFound.Column), wsUD.Cells(Rows.Count, rFound.Column).End(xlUp).Offset(1)).Copy Destination:=rCell.Offset(1)
If that is not what you meant, please clarify further.
 

Forum statistics

Threads
1,081,716
Messages
5,360,816
Members
400,599
Latest member
imarler

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top