+ Reply to Thread
Results 1 to 5 of 5

Separating issues from multiple rows into columns

  1. #1
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.97.2 for Mac MS 365
    Posts
    8,711

    Separating issues from multiple rows into columns

    I have an interesting problem I've been presented with and rather than try formula after formula I thought I'd propose it to you all (see attached sheet).
    I have 7269 rows with 1930 distinct claims. I need to pull out the Primary issue (done that) then across from it put in each and every secondary issue (from col C). The largest # of claim lines is 89 (see E1). So in theory I need to find that claim and put all the secondary issues in the next 89 columns from row 4990 beginning in col H.
    I've done a couple examples of what I'm looking for in rows 2, 4 and 5 and 8 but beyond that ...
    So every place where there is a value in col F I'd like the list of secondary issues in the same row. Help will be appreciated.
    Attached Files Attached Files
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Separating issues from multiple rows into columns

    I would maybe try something like:
    =IF(SMALL(IF($A$2:$A$7270=$A2,1,10000)*IF(COUNTIF($G1:G1,$C$2:$C$7270)=0,ROW($A$1:$A$7269),10000),COLUMN(A1))>=10000,"",IF($F2="","",INDEX($C$2:$C$7270,SMALL(IF($A$2:$A$7270=$A2,1,10000)*IF(COUNTIF($G1:G1,$C$2:$C$7270)=0,ROW($A$1:$A$7269),10000),COLUMN(A1)))))
    as an array formula and copy down and across as neccessary.

    It will probably take a long time to calculate though, this sort of thing is probably more suited to VBA..

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.97.2 for Mac MS 365
    Posts
    8,711

    Re: Separating issues from multiple rows into columns

    thanks, have to go into a meeting and will test it when I get out.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.97.2 for Mac MS 365
    Posts
    8,711

    Re: Separating issues from multiple rows into columns

    I am getting #NAME? in col H and #NUM! in cols I, J and K.
    One thing is that I found an error in the original data that I was given so the person who ran it is going to rerun it which should decrease the number of duplicate rows.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.97.2 for Mac MS 365
    Posts
    8,711

    Re: Separating issues from multiple rows into columns

    Ok, didn't notice your note that it is an array formula until just now.
    That being said, it now works fine!
    Thanks and I'll mark it solved and bump your rep.

+ 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. Separating Grouped Rows into different Columns
    By mrlau in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-07-2013, 03:48 AM
  2. [SOLVED] Separating data with multiple delimiters into columns AND rows
    By CALALOO722 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2013, 08:13 AM
  3. Separating rows from one column to multiple columns in Excel 2007
    By AleksSkrzypczyk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2013, 11:19 AM
  4. Replies: 3
    Last Post: 05-03-2010, 11:23 AM
  5. Replies: 3
    Last Post: 11-02-2005, 06:17 PM

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