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]

If you pm me an e-mail address I'll send you the file with any significant data removed (It's easier than trying to take a small useful section of the data :P ) - I'll replace the barriers with the column reference and just delete the locations before sending it to you.

 

[FDibbins]

Not the normal way of doping things, but I feel that this has got to the point where that is about the only option left. email addy has been sent to you in PM

 

[FDibbins]

OK, I got your file, thanks.

I think you had my formula a bit mixed up, and as I thought, your ID numbers are text, not numbers. Not sure what you are doing with the 1st few columns, but see if this will help get you started - I based it off Barriers sheet...
=IFERROR(INDEX(BarriersSub!C:C,SMALL(IF(TEXT($A$1,"000")=Barriers!$A$2:$A$686,ROW(Barriers!$A$2:$A$686)),ROWS($A$1:A1))),"")
ARRAY entered using CTRL: SHIFT ENTER, then copied down.

You should be able to just copy this across too, provided your columns all match (in sequence)

 

[Hordern]

Many Many Thanks Ford

I've not had a chance to try this yet as I was ill yesterday and today it turns out the new permanent staff member in the team doesn't have a computer, so I get to do whatever I like today while she uses my work station. I shall however keep hold of the info for my own knowledge and purposes and assuming it works, you'll have a belated hug coming your way when I've had a chance to test it!

Cheers