+ Reply to Thread
Results 1 to 6 of 6

Complex data reorganization question

  1. #1
    Registered User
    Join Date
    09-09-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    3

    Complex data reorganization question

    (I apologize if I'm not in the right place on the forum, but this seemed to be a good place to ask this.)

    I have a 30x30 table where an 'x' has been put in a fair number of the cells. I need to end up with the column headers of each cell with an 'x' next to the row leader of each cell with an 'x', but on separate lines.

    For instance, on row 3, cells 3B, 3C and 3F have x's in them. The the column headers of columns B, C and F would end up on separate lines next to the row leader of row 3. The finished product for row 3 would look like:

    3 leader | B header
    3 leader | C header
    3 leader | F header

    I've attached a small sample spreadsheet for (hopefully) further clarity re the problem.

    Thanks in advance for any help/guidance you can give me!

    -sd
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,066

    Re: Complex data reorganization question

    Hi lgamtech and welcome to the forum,

    A little subroutine in VBA will do your problem. See the attached and look at the code of:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Complex data reorganization question

    This solution uses a helper column immediately to the right of the table (Lookup Array), a standard formula referring to the Lookup Array for generating the extracted row headers and an array formula for extracting the column headers.

    Beau Nydal
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Complex data reorganization question

    Marvin: One small problem with this, although it's definitely right there. I get a Method 'Range' of object '_Global' failed message when I run this on my actual spreadsheet. The debugger says the issue is with this line:
    TopRow = Range("XRange").Row

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,066

    Re: Complex data reorganization question

    Hi - Sorry - I cheated a little bit.
    I created a Named Range of your range with the data and called it XRange. Look at the named ranges area and see what I did.

    http://www.contextures.com/xlNames01.html

    You will need to create this same name on the range of your real data for my macro to work correctly.
    Last edited by MarvinP; 09-09-2011 at 07:41 PM.

  6. #6
    Registered User
    Join Date
    09-09-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Complex data reorganization question

    Got it! And it works! And....<drum roll> this will save me HOURS of time this weekend and in the future! Again, thanks!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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