Extracting the nth non-blank cell in a row

**arwright16** · 07-11-2013, 12:07 AM

Dear all

I am a new user to the forum, and have already found your threads very useful!

I was hoping that you could help with this problem:

I have a spreadsheet with lots of students names (I am a teacher) and on each row there are the classes that they attend. Since each student does not attend every class, there are a lot of blanks in each row, as each class is listed in columns. I need a formula to extract the nth non-blank cell in a row, so I can extract the cells into a collection of columns based on each department in the school. For example, student A attends Art class 1, then there are 4 blanks and then they attend English class 2. Student B has a blank in their row, then they attend Art class 2, then 2 blanks as they attend English class 1. When all the data is extracted from each row, I should have a column for Art which is completely full, and below it should say 7Art1 (for student A), 7Art2 (for student B) etc. If I can extract the nth non-blank cell then I can tell the Art column to look for the 1st non-blank cell, the English column can look for the 2nd non-blank cell and so on...

I hope this makes sense.

Any help would be much appreciated!

**dilipandey** · 07-11-2013, 01:19 AM

Hi arwright,

welcome to the forum.

Can you upload a sample workbook along with your expected results? Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**azumi** · 07-11-2013, 02:31 AM

Yeah Bro.... please upload your sample workbook

**arwright16** · 07-11-2013, 03:38 PM

Hi everyone

Thanks for the quick reply!

Please find the workbook attached. I have Office 365, so I use the latest version. I have provided the "before" raw data, and then an "after" tab which hows some of the data. I am the data analyst for my school and really need to pull together the horribly organised data I am supplied with from our database into a useable format. I would prefer a formula method if possible as I have not used VBA, but would love to learn more formula stuff!

Your help is greatly appreciated!

Thanks.

**oeldere** · 07-11-2013, 04:21 PM

Maybe this one can be a start for solving your problem.

Take a look at this file.

I used the attached macro to get the result.

**arwright16** · 07-11-2013, 04:40 PM

Hi

Thanks for that. Ideally I would like one row per student though, with all their classes going horizontally (with no gaps).
Is there a way to tweak your work to do it this way?

Thanks again

**oeldere** · 07-11-2013, 04:54 PM

So for all of these values you want an column?

(I don't know if I can help you enough with it; but we gonna try it).

AR
EN
FM
FR
GG
GS
HI
IT
MA
MU
PE
RE
SC
TE
ES
SB

2nd question about this item

and what about duplicated values: like this below

A GG\232 7Ge1 GG
A GG\373 7Ge1 GG

How should that be stored in your file?

**arwright16** · 07-12-2013, 12:27 AM

Hi

Yes please - each of the values represents the code for a subject e.g. MA = Mathematics (sorry, I should have said this).

Could duplicates be removed if possible?

thanks again

**oeldere** · 07-12-2013, 07:59 AM

Please Login or Register  to view this content.

A GG\232 7Ge1 GG
A GG\373 7Ge1 GG

above example counts als duplicated? (or not)?

**arwright16** · 07-12-2013, 12:36 PM

Hi

A GG\232 7Ge1 GG
A GG\373 7Ge1 GG

Yes they do count as duplicates - they both mean Year 7 Geography class 1

Thanks again - it is greatly appreciated!

**oeldere** · 07-12-2013, 01:08 PM

I will ask other forummember to help us with this problem.

I post the link afterwards. (#249)

http://www.excelforum.com/the-water-...00#post3320500

**JBeaucaire** · 07-12-2013, 08:36 PM

This is an array formula, it is designed to find each non-blank cell for each student and list them in order across their row on AFTER sheet. This formula is entered into B2:

=IFERROR(INDEX(OFFSET(sheet2!$A$1,MATCH($A2,sheet2!$A$2:$A$14,0),1,,200),SMALL(IF(OFFSET(sheet2!$A$1,MATCH($A2,sheet2!$A$2:$A$14,0),1,,200)<>"",COLUMN(OFFSET(sheet2!$A$1,MATCH($A2,sheet2!$A$2:$A$14,0),1,,200))-1,""),COLUMNS($B$1:B$1))), "")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Once that is entered correctly and the first correct value appears, copy that cell down, then across to the right as far as needed.

I've entered this formula across through columnY, enough for 24 classes. I know this doesn't organize by class type, but this gives you your student's classes. You could use this table as a second refernce to build a class type schedule, as separate project.

Extracting the nth non-blank cell in a row

LinkBack

Thread Tools

Rate This Thread

Display

Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Re: Extracting the nth non-blank cell in a row

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions