+ Reply to Thread
Results 1 to 8 of 8

Formula(s) To Convert/Split Row Into Multiple Rows Failing

  1. #1
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Question Formula(s) To Convert/Split Row Into Multiple Rows Failing

    Hello Excel Experts,

    Have run into a silly problem trying to convert a ROW of data into multiple rows using formulas MATCH, INDEX, OFFSET, SUMPRODUCT, COUNTIF etc.

    Basically when it reaches a record without DATA it seems to break and stops for all future records, repeating the last record where the break occurred.

    This is an example data set;

    FULLNAME Contact1 Contact2 Contact3
    Full1 Name1
    Full2 Name1 Name2
    Full3
    Full4 Name1

    Which I would like to then produce;

    FULLNAME Contact
    Full1 Name1
    Full2 Name1
    Full2 Name2
    Full3
    Full4 Name1

    However instead I'm getting;

    FULLNAME Contact
    Full1 Name1
    Full2 Name1
    Full2 Name2
    Full3 #NUM!
    Full3 #NUM!
    Full3 #NUM!
    Full3 #NUM!
    ..

    Please see example on row 184 of the SPLIT Worksheet

    I've made a mistake with the formula so I wonder if somebody could take a quick look and let me know where I've gone wrong.
    As the original datasheet contains formulas instead of text values I used SUMPRODUCT for the count which may be the problem.

    Thanks all,

    Nic
    Attached Files Attached Files
    Last edited by nisiwi; 02-17-2014 at 04:18 PM.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Formula(s) To Convert/Split Row Into Multiple Rows Failing

    Hi,

    Yes. You don't seem to have accounted for cases where all contacts are blank for a given company. This array formula in A2 in an alternative which should avoid that issue:

    =INDEX(DATA!$A$2:$A$200,MATCH(TRUE,MMULT((--(ROW(DATA!$A$2:$A$200)>=TRANSPOSE(ROW(DATA!$A$2:$A$200)))),COUNTIF(OFFSET(DATA!$B$1:$I$1,ROW(DATA!$A$2:$A$200)-MIN(ROW(DATA!$A$2:$A$200))+1,,,),">"""))>=ROWS($1:1),0))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Re: Formula(s) To Convert/Split Row Into Multiple Rows Failing

    Thanks XOR LX, looks good. I'll give it a try and wrap my head around it :-)
    Your assistance is welcome and very much appreciated!

  4. #4
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Re: Formula(s) To Convert/Split Row Into Multiple Rows Failing

    I tried the formula as you suggested XOR LX, however I'm getting a #VALUE! result in A2.
    See attached spreadsheet.
    Attached Files Attached Files

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Formula(s) To Convert/Split Row Into Multiple Rows Failing

    Apologies. I normally specify that a given formula is to be array-entered**, though I presumed you'd know that in this case.

    See below description if you're not sure how to do this.

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

  6. #6
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Re: Formula(s) To Convert/Split Row Into Multiple Rows Failing

    My mistake, I should have picked that up XOR LX (multi tasking as usual) as your approach is using an Index to find the record in question where as I was using the previous record.

    It's pretty resource intensive for the under specified laptop I'm running this workbook on so I'll have to switch to another machine to get any output.
    Last edited by nisiwi; 02-19-2014 at 07:01 AM.

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Formula(s) To Convert/Split Row Into Multiple Rows Failing

    Yes, on a large dataset that set-up may well be quite resource-hungry. Perhaps consider a VBA solution if performance becomes an issue?

    Regards

  8. #8
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Re: Formula(s) To Convert/Split Row Into Multiple Rows Failing

    I'm happy to look at VBA (my Laptop just won't cut it) although I'm no expert VBA.

    Can you point me in the right direction XOR LX ?
    Last edited by nisiwi; 02-19-2014 at 04:26 PM.

+ 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. split multiple semicolon separated values into new rows for multiple columns
    By bruno08102013 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-05-2014, 05:27 PM
  2. I want to split row data to multiple rows
    By SumitM in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2013, 05:37 AM
  3. Split a row of data into multiple rows
    By chuckyfang in forum Excel General
    Replies: 2
    Last Post: 03-19-2013, 06:13 PM
  4. Split multiple values in a cell to multiple rows
    By karmithr in forum Excel General
    Replies: 2
    Last Post: 03-04-2013, 08:34 PM
  5. [SOLVED] Split one row into multiple rows
    By neo4u44 in forum Excel General
    Replies: 4
    Last Post: 05-28-2012, 11:37 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