+ Reply to Thread
Results 1 to 9 of 9

Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

  1. 05-09-2012, 10:48 AM #1
    Jahoker
    Jahoker is offline
    Registered User
    Join Date
    05-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Post Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

    Hello,

    I found this forum on Google after trawling for suitable methods of completing a task I've come up against.

    The Task:
    Essentially I have a sheet with 10000~ rows and 2 columns. First I need to match cell data in column A (NetBIOS names) and where there's a match I need the data transposed from column B into column C,D,E,F etc where applicable.

    I have attached a small sample of the workbook, sheet1 (Thanks) which shows the data for the task at hand, sheet2 (Expected End Result) is what I hope the data would look like when the macro has been run.

    If there's an easier solution that I'm missing, I am all ears.

    Thanks in advance, James
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-09-2012, 01:27 PM #2
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

    James,

    Using your sample file, give this a try:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 05-10-2012, 04:25 AM #3
    Jahoker
    Jahoker is offline
    Registered User
    Join Date
    05-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

    EDIT - Tiger, I tried running this against the full data set within the sample document and I receive:

    Run-Time error '7'
    Out of Memory

    If I select to Debug, it highlights the following line of code on Ln28:
    ReDim arrResults(1 To UBound(arrUnq, 1), 1 To Columns.Count)

    Any clue as to why that could be? I've ensured the data is free of any #NA and blanks.

    Help appreciated, cheers!

    Tiger, thank you VERY much, your solution provided me everything I need (but only when running against the limited sample. Absolutely superb...

    (Purely out of curiousity, if you get 5 minutes could you add some comments where appropriate - I need to learn this!)

    Rep given!
    Last edited by Jahoker; 05-10-2012 at 04:44 AM. Reason: update - jumped too quickly!
    Reply With QuoteRegister To Reply
  4. 05-10-2012, 06:10 AM #4
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

    See if this formula approach helps.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
    Reply With QuoteRegister To Reply
  5. 05-10-2012, 10:50 AM #5
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

    Jahoker,

    Code with comments. Made a slight change regarding dimensioning the results array to help avoid that error. 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 05-11-2012, 02:10 AM #6
    shiven.k
    shiven.k is offline
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

    Hey Guys help me too...

    I have data in this format and want to arrange in the given format:
    Data Have:
    AC-No. Date Absent
    114 21/03/2012
    114 22/03/2012
    114 23/03/2012
    114 24/03/2012 True
    114 26/03/2012 True
    114 27/03/2012
    114 28/03/2012
    114 29/03/2012
    273 21/03/2012
    273 22/03/2012
    273 23/03/2012
    273 24/03/2012 True
    273 26/03/2012
    273 27/03/2012 True
    273 28/03/2012
    273 29/03/2012
    1030 21/03/2012
    1030 22/03/2012
    1030 23/03/2012
    1030 24/03/2012 True
    1030 26/03/2012

    Now, I want to arrange the data in this format

    Emp_cd 21 22 23 24 25 26 27 28
    114 0 0 0 A 0 A 0 0
    273 0 0 0 A 0 0 A 0
    1030 0 0 0 A 0 0 0 0
    1084 0 0 0 0 0 0 0 0
    Reply With QuoteRegister To Reply
  7. 05-11-2012, 06:11 AM #7
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

    Shiven.k,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  8. 05-11-2012, 06:35 AM #8
    shiven.k
    shiven.k is offline
    Registered User
    Join Date
    05-10-2012
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    30

    Re: Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

    Apologies, will keep in mind for future,

    Thanks
    Reply With QuoteRegister To Reply
  9. 05-15-2012, 10:37 AM #9
    Jahoker
    Jahoker is offline
    Registered User
    Join Date
    05-09-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Match cells in Column A, then transpose (matching) row data from column B, C, D, etc.

    Tiger, thanks very much for the second script, it worked a treat!

    A+
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1