+ Reply to Thread
Results 1 to 10 of 10

Copy data between sheets having same col names but not col numbers

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Copy data between sheets having same col names but not col numbers

    I've attached a sample workbook with two pages. The purpose of the workbook is to allow me to copy/paste .csv data into the workbook, then automatically "Normalize" it on another page so that the Normalized page always has the same column headers in the same place, even if the .csv data is missing (or adding) certain columns.

    The sample workbook gives you several potential .csv layouts in Sheet1 to illustrate 3 variations of these files. The Normalized page shows the layout I want so that I can do calcs on all of my .csv files automatically, without having to modify formulas or move columns around whenever the .csv files differ in their individual layouts.

    I tried using HLOOKUP to "find" the matching heading (if there is one) but it is not returning the expected values. Also, I can't get it to insert a blank cell (or a "" cell) if Sheet1 is empty for that cell.

    Any advice? Should I use a combination of MATCH and LOOKUP?

    Thanks!
    Attached Files Attached Files
    Last edited by jrtaylor; 05-01-2020 at 04:32 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: I'm having trouble getting HLOOKUP to work

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not that something doesn’t work.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: Copy data between sheets having same col names but not col numbers

    That’s better - thanks.

    Do the columns share the same names, even though they are in a different order?

  4. #4
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: Copy data between sheets having same col names but not col numbers

    Thank you. The attached workbook provides examples of the variations in the column names/layouts. The names will always be the same, but in some cases the .csv may be missing a particular column, and sometimes this occurs in column 1, sometimes in other columns. You'll see this in the examples of Sheet1 of the workbook.

    On another note, I've never used HLOOKUP before, and was a bit surprised that if I copied it down, the row number was not relative. It remained "2" when I copied my formula from row 2 down...

    I appreciate your help. Having worked with Excel from the very first version decades ago, I should know. But I don't!!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: Copy data between sheets having same col names but not col numbers

    This seems a prime candidate for PowerQuery, which is built into Excel 2016.

    I would put all the .csv files into one folder and have Excel import them into PowerQuery, where they would be appended by matching columns with the same name to a query that contains all they column headers you need in the consolidated sheet.

    I can’t demonstrate this now as I am on my iPad.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Copy data between sheets having same col names but not col numbers

    I used this formula in F2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I would copy>paste Special >Formulas across so that your formatting in the different columns doesn't get screwed up.

    I have the IF statement to avoid putting in 0's where you don't want them. Alternately, you could format zeros to not show. Is this what you are looking for?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: Copy data between sheets having same col names but not col numbers

    Thanks AliG. For several reasons I can't use the PowerQuery solution. The "Normalized" tab is there so that I can perform some heavy-duty calcs that slow down Excel considerably even with four or five .csvs (which can be 15,000 rows deep). And also in my experience with PivotTables and PowerQuery, I've had trouble doing calcs.

    ChemistB, your solution works. I modified it by including an "IFERROR" function so that if a column title is missing in the .csv, I don't get a #NA error on the first sheet.

    I appreciate the help from both of you.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: Copy data between sheets having same col names but not col numbers

    I have no problem doing calculations alongside PQ, but go with whatever you feel more comfortable with.

  9. #9
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: Copy data between sheets having same col names but not col numbers

    Hi Ali,

    I meant to say that I have probs with calcs on pivot tables. PQ is fine. I'm going to see if I can use it per your suggestion.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: Copy data between sheets having same col names but not col numbers

    Ah, I see. Let us know how you get on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hlookup won't work
    By mufan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2018, 09:54 AM
  2. [SOLVED] HLOOKUP doesnt work in all rows
    By Immortal2014 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-20-2015, 04:58 PM
  3. Having trouble with Vlookup/Hlookup
    By indians207 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-27-2013, 09:04 PM
  4. Can't get the HLOOKUP function to work properly
    By dennisvc in forum Excel General
    Replies: 1
    Last Post: 05-07-2012, 03:12 PM
  5. Lookup where v & hlookup just won't work!
    By Wilgoss in forum Excel General
    Replies: 11
    Last Post: 06-03-2011, 01:49 PM
  6. hlookup work if exists
    By Latszer in forum Excel General
    Replies: 1
    Last Post: 10-23-2009, 01:01 AM
  7. My HLookUp won't work
    By dominicb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2008, 08:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1