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.
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.
