Horizontal indexing from large array

[Hordern]

Hi Folks

I've only been getting to grips with tougher formulae in the last 36 hours and I've achieved most things that I wish to - partly thanks to some wonderful explanations in these forums. However, I'm struggling on the last step and I can't find a thread that answers it.

I have an array that is over 600 rows deep and around 30 columns across. Column A is an location ID number, B is irrelevant to this query. C contains a submitter ID number and D is also irrelevant. Every column after that is a feature of the location and where a submitter has commented on that feature at that location, I have included their submitter number.

I have multiple rows with the same locations, and multiple rows with the same submitter, but never a repeat of a location and submitter.


On another page I am listing the location ID in A. B is irrelevant to this, C has the column headers from the array (these will be copied again and again as necessary) and D has the number of submitters who have commented about the feature in the location (I used this: =COUNTIF(BarriersGeo!E:E,A102) where BarrieraGeo is the tab name, E:E is the feature column and A102 is the location ID within the indexing tab).

Now in E I am trying to list the submitters who have contributed to that location and feature.

I managed to get it to work where there was only 1 submitter for a given location ID by using:

=VLOOKUP(A102,BarriersSub!A2:AL686,17,0)

Where 17 is a feature column.


After playing with google and some tutorials I got as far as the following with CSE to make it an array:

=INDEX(BarriersSub!$E$2:$E$686, SMALL(IF($A$102=BarriersSub!$A$2:$A$686, ROW(BarriersSub!$A$2:$A$686)-ROW(BarriersSub!$A$2)+1), ROW(1:1)))

When I drag this down the column, I get a cell for every time the location ID is listed in the array table. The cell is blank if the submitter did not choose the feature being indexed and the submitter number. The cell has the submitter number if the submitter commented on that feature.

The MAIN thing I'd like to fix is being able to drag the formula horizontally (I tried playing with ROW and COLUMN but got nowhere). Preferably with an IF in the correct place to ignore the blank references [I'm 99% sure this can be done]. Ideally I'd like all the submitters to be listed in one cell but that doesn't really matter very much and I'm not expecting that to be possible.

My mind is melting right now as up until 2 days ago the most I'd done was use SUM and TOTAL...

I hope this makes sense and thank you so much in advance.

 

[Hordern]

OK I see 1 error there...
=IFERROR(INDEX(BarriersSub!$E$2:$E$686,SMALL(IF($A$102=BarriersSub!$A$2:$A$686,ROW(BarriersSub!$A$2:$A$686)),COLUMNS($A$ 1:A1))),"")

If you notice, in my suggested formula, I used the entire column there E:E, not a range.

Try that and see if that fixes it

Also, I thought you had 2 criteria to test against? Your formuyla just shows 2
H2=IFERROR(INDEX($C:$C,SMALL(IF(($A$2:$A$10=$F$1)*($B$2:$B$10=$F$2),ROW($A$2:$A$10)),COLUMNS($A$1:A1))),"")

Ach, I'd typed a reply and then hit reply to thread instead of post quick reply >.<

Anyway, Thank you so much for the column suggestion rather than range. That fixed the errors I was getting - AWESOME.

As for the second criteria, I can see why you thought I was asking for that in the OP, but all I'm trying to do is search by GeoID and retrieve every submitter number within a barrier column who mentioned said GeoID. The only improvement left now is a way to ignore blank cells. That would effectively be the second criteria. I thought perhaps something along the lines of *(BarriersSub!$E$2:$E$686=TRUE) but it doesn't work. Whether this is because it's some noob excel rookie error, or because every cell in the table is a formula, I don't know

Anyway, the error is fixed and for that you get another hug!

If you can figure out a way to ignore the blanks, I'll send you another

 

[FDibbins]

The only improvement left now is a way to ignore blank cells.

Your formula...
=IFERROR(INDEX(BarriersSub!$E:$E,SMALL(IF($A$102=BarriersSub!$A$2:$A$686,ROW(BarriersSub!$A$2:$A$686)),COLUMNS($A$ 1:A1))),"")
should ignore anything that does not match A102?

 

[Hordern]

Your formula...
=IFERROR(INDEX(BarriersSub!$E:$E,SMALL(IF($A$102=BarriersSub!$A$2:$A$686,ROW(BarriersSub!$A$2:$A$686)),COLUMNS($A$ 1:A1))),"")
should ignore anything that does not match A102?

It kind of does, in so far that any rows that have different GeoID's are ignored, but it still draws in blank cells where other submitters have mentioned different barriers at the same GeoID. Here's a screenshot:

 

[FDibbins]

Hard to see from that SS, cant see column letters, but maybe try adding an extra test (like my 2nd test) to check for no entries?

 

[Hordern]

The column letters shouldn't matter in that screenshot (I think...) - that's the output tab, not the reference table.

For what it's worth they're the same as this one from earlier though since then I've resized a couple of the columns:

 

[Hordern]

For some reason the whole system (except the 'number of mentions' column) has broken as I get to GeoID100 and on... It works if I change the GeoID's to 0100 etc. I have all the cells marked as text because I was struggling with confusion in the system with 19 and 119 and similar before I did that.

I guess it's an easy manual fix, but irritating.

 

[Hordern]

And as a final addition, making those changes (adding another 0 infront of all of the GeoID's) broke everything :P Fortunately I had saved before I made the changes....

Perhaps starting from 1000 and making my way up from there to finish on 1600 (or thereabouts) would work... Something to check for the next one of these projects I do!

 

[FDibbins]

Apologies for the delay in replying, I was not able to log in here for a while

And as a final addition, making those changes (adding another 0 infront of all of the GeoID's) broke everything :P Fortunately I had saved before I made the changes....

Perhaps starting from 1000 and making my way up from there to finish on 1600 (or thereabouts) would work... Something to check for the next one of these projects I do!

That's because your current ID's are not numeric, they are text, looking like a number. Any time you see a number starting with 0, chances are, it's text. By trying to add a 0 at the front end, you are effectively converting it to a number...027 becomes 27 if you try and make it 0027 - and as far as excel is concerned, 0027 (or even 027) is not the same as 27

To make it 0027, you need to start with '0027

If you want to just use real numbers for the ID, but still want 3 (or 4) places shown, you could use Custom Formatting to do that for you

 

[Hordern]

Apologies for the delay in replying, I was not able to log in here for a while

That's because your current ID's are not numeric, they are text, looking like a number. Any time you see a number starting with 0, chances are, it's text. By trying to add a 0 at the front end, you are effectively converting it to a number...027 becomes 27 if you try and make it 0027 - and as far as excel is concerned, 0027 (or even 027) is not the same as 27

To make it 0027, you need to start with '0027

If you want to just use real numbers for the ID, but still want 3 (or 4) places shown, you could use Custom Formatting to do that for you

No worries, and thanks for coming back.

I realised one fundamental error was some incorrect sorting in the first reference tab. Once I sorted that, I was able to get everything working absolutely correctly barring removing the pesky blank cells, but thankfully that really doesn't matter much.

Here's an enthusiastic hug of thanks for all of your help: \o/

 

[FDibbins]

aahh fanx Im happy I was able to help

Ya know, Im still curious about those blanks. Anyway you could post some sample data that I could play with?