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

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

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).

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.

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

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 ?