+ Reply to Thread
Results 1 to 5 of 5

find header value in column, return cell next to it for copy/paste

Hybrid View

  1. #1
    Registered User
    Join Date
    09-12-2014
    Location
    montreal, canada
    MS-Off Ver
    2013
    Posts
    3

    find header value in column, return cell next to it for copy/paste

    HI,

    This is my first post ever in a forum. I am using Excel 2013. I have customer data in a few rows and columns which repeat every 10 lines. I would to have that data transposed to a new sheet with only one row per record. I know that this can be done with a macro instead of moving every cell individually as I have 1300 lines to go through. I am new to macros but have a basic understanding of vba.

    The data is as follows (for example) with empty or not needed info in between relevant information.

    Column A Column B Column C Column D
    Row 1 Customer : JUL01Q ZIP Code: 1A2 C3D
    Row 2 Title :
    Row 3 Name : Julz design
    Row 4 :
    Row 5 Address : 123 Avenue A
    Row 6 :
    Row 7 City : Montreal
    Row 8 : QC
    Row 9 Country : CA Canada
    Row 10 Area : QC01
    Row 11 Customer : JUL02Q

    Etc.....

    I would like it to look like this instead

    Column A Column B Column C Column D Column E Column F Column G Column H
    Row 1 (header) Customer Name Address City Province/State Country Zip code Area
    Row 2 JUL01Q Julz Design 123 Avenue A Montreal QC CA 1A2 C3D QC01

    and so on.

    Please accept my apologies in advance if I am not following all the rules of posting as well as my thanks for any help you send my way.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,258

    Re: find header value in column, return cell next to it for copy/paste

    It is easier to do this with formulas, believe it or not - IF your data is consistent.

    Insert a new Row 1, and starting in column E, place your headers in row 1.
    Then, below each header, in row 2, create a link to the value in columns A to D of rows 2-11 that contain the value that you want for that header.
    Then, copy E2:L11, select your 1300 or so rows, and paste.

    Then, copy E:L and paste special values, and sort to remove the blanks. Then delete columns A:D, and you're done.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    09-12-2014
    Location
    montreal, canada
    MS-Off Ver
    2013
    Posts
    3

    Re: find header value in column, return cell next to it for copy/paste

    Thank you for the quick reply. I have have must be doing something wrong because no matter what i do, i always get the same information repeated on each line rather than a new record.

    What formula is used to link the value? I am using = B2 (for example). Should it be something different?

    TX Julz

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,258

    Re: find header value in column, return cell next to it for copy/paste

    I made up an example workbook that should help you....

    Make rows from columns.xlsx

  5. #5
    Registered User
    Join Date
    09-12-2014
    Location
    montreal, canada
    MS-Off Ver
    2013
    Posts
    3

    Re: find header value in column, return cell next to it for copy/paste

    thank you. I appreciate your quick reply again. Have a great day.

+ 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. [SOLVED] To find first non blank cell in a row then return column header
    By divi123 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-11-2022, 04:49 AM
  2. To find first non blank cell in a row then return column header
    By divi123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 07:14 AM
  3. [SOLVED] Find specific value in table and return header row and header column value
    By nelwan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-30-2013, 01:35 AM
  4. [SOLVED] Find last non blank cell in a row then return column header
    By Chad B in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2013, 10:33 AM
  5. .Find header and column in another workbook and copy the cell that matches both
    By Ariadust in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2012, 12:15 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