Sorting columns to remove blanks on 1 column, matching with other columns

Hi guys, was wondering if anyone could help me with a few problems with sorting with array formulas. Basically I want to be able to remove all blanks from a set of columns, which have in them the category name and the values. I only stumbled upon array formulas last week, so I really have no idea what I’m doing.

The labels are in column A1:A7, and the first set of values in Column C1:C7. As both the labels and values will come from a separate page later and will not necessarily have data in each cell, there will be blanks in a few of the rows, which I need to remove before I put the data into graphs.

I’ve managed to sort both columns to remove the blanks next to them in columns B and D, with the following array formula copied into B1 and copied down to B7:

{=INDEX($A$1:$A$7,SMALL(IF(ISTEXT($A$1:$A$7), ROW($A$1:$A$7),""),ROW(1:7)))}

And the following in D1, copied down to D7:

{=INDEX($C$1:$C$7,SMALL(IF(ISNUMBER($C$1:$C$7),ROW($C$1:$C$7),""),ROW(1:7)))}

The problem I can see with the way I’ve done this is that if there were no value in column B next to one of the labels in column A, the labels and values would not correspond after they are sorted. This is what happens when I add the second set of values in a third column, where there are some blank values in rows with labels.

My first question is this: can array formulas be used to sort on one column, the labels column, to remove the blanks, and sort the rest of the columns in the same way, keeping the values in the right rows i.e. corresponding to the correct labels?

The way I’ve gotten around this at the moment is a very convoluted “IF” formula. I’ve copied the original data to column F for the labels, and columns G and H have two separate sets of values. In column J, the data from column F has been sorted with the array formula as before. To get the values to stay with the right labels, the following formula is entered into K2:

=IF(N1=F$1,G$1,IF(N1=F$2,G$2,IF(N1=F$3,G$3,IF(N1=F$4,G$4,IF(N1=F$5,G$5,IF(N1=F$6,G$6,IF(N1=F$7,G$7,"")))))))

And is copied down to K7, and across into column L. This formula does the job for now, but as the finished spreadsheet will have a lot more than 7 rows, I don’t think nesting the formula like this will cut it.

So, my second question is this: is there some sort of array or other type of formula that can be entered into columns K & L to do the function of the formula I showed above in a much simpler way?

I hope what I’ve said makes sense, any help at all would be much appreciated. I’ve attached the file if anyone wants to have a look.

Cheers

Hi macca345
Welcome to the forum
You Could just do a index/match
K1:
=INDEX(G$1:G$7,MATCH(J1,F$1:F$7,0),1)
fill down
L1:
=INDEX(H$1:H$7,MATCH(J1,F$1:F$7,0),1)
Fill down

no problem
If you are satisfied with the solution(s) provided, 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