Looking up Data in a relatively blank Sheet

[peteatlab]

Hey everyone,


I'm trying to write a formula that will condense data that's sparse in an otherwise blank data sheet. In a study my research lab did, participants were assigned to 1 of 12 conditions and asked two questions after that. There responses were recorded in a 24 column sheet, with each condition that a participant was assigned, having two entries in the corresponding columns. The result is that for each participant, there are 22 columns with no data, and 2 columns with their answer. I want to find some way to write a formula that condenses it all into the same two columns, but so far I've been unsuccessful. I've included a picture attachment that should show what it looks like. Any help would be really, really appreciated.

Thanks!
pete

[NBVC]

Assuming your first row range is M3:AZ3, try these:

=INDEX(M3:AZ3,MATCH(TRUE,INDEX(M3:AZ3<>"",0),0))

and

=INDEX(M3:AZ3,MATCH(TRUE,INDEX(M3:AZ3<>"",0),0)+1)

next to each other and copied down

These will extract the first and next items in each row...

[peteatlab]

Thanks so much! That certainly worked!

A follow up question though: How would I modify this so that if all columns within the designated space are blank, the cell i'm programming is left blank? Currently, if that's the case, I'm given N/A. I tried to embed this statement in an IF(ISBLANK( statement, but that didn't work... any thoughts?

[NBVC]

Try:

=IF(COUNTA(M3:AZ3)=0,"",INDEX(M3:AZ3,MATCH(TRUE,INDEX(M3:AZ3<>"",0),0)))

and


=IF(COUNTA(M3:AZ3)=0,"",INDEX(M3:AZ3,MATCH(TRUE,INDEX(M3:AZ3<>"",0),0)+1))

[peteatlab]

You sir, are a king among men.

[NBVC]

Umm... Thank you ... and you're welcome.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save