Hello All
I was wondering if you could help me alter this code that someone wrote on here previously
My data comes from the Download sheet, the top table with the black cells is the rewuired answers, the bottom table with the 3 red cells is the ones i require the formuls to be re written if you could help please.
could you please change the formula above to find the second and third answer in the list,
mant thanks Jason
| output_20210128T133123_000001jay.xls | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ||||||||||
| 2 | ||||||||||
| 3 | ||||||||||
| 4 | 28/01/2021 | Joe Bloggs | 9999 - Test - Houseblock B | Detail 1 | 2 | £77.00 | £154.00 | |||
| 5 | 28/01/2021 | Joe Bloggs | 9999 - Test - Houseblock B | Detail 2A (First Visit) | 30 | £88.00 | £2,640.00 | |||
| 6 | 28/01/2021 | Joe Bloggs | 9999 - Test - Houseblock B | Detail 3 | 6 | £99.00 | £594.00 | |||
| 7 | 29/01/2021 | Joe Bloggs | 9999 - Test - Houseblock C | 1 | £33.00 | £33.00 | ||||
| 8 | 39 | £3,388.00 | ||||||||
| 9 | ||||||||||
| 10 | 28/01/2021 | Joe Bloggs | 9999 - Test - Houseblock B | Detail 1 | 2 | £77.00 | £154.00 | |||
| 11 | 28/01/2021 | Joe Bloggs | 9999 - Test - Houseblock B | 30 | £88.00 | £2,640.00 | ||||
| 12 | 28/01/2021 | Joe Bloggs | 9999 - Test - Houseblock B | 6 | £99.00 | £594.00 | ||||
| 13 | 29/01/2021 | Joe Bloggs | Detail 4 | 1 | £33.00 | £33.00 | ||||
| 14 | 39 | £3,388.00 | ||||||||
| 15 | ||||||||||
| 16 | First in the list | |||||||||
| 17 | First in the list | |||||||||
| 18 | second in the list | |||||||||
| 19 | Second in the list | |||||||||
| 20 | ||||||||||
| 21 | Third in the lists | |||||||||
| 22 | ||||||||||
Sheet2 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D4,D10 | D4 | =IFERROR(INDEX(Download!J:J,AGGREGATE(15,6,ROW(Download!J:J)/(COUNTIF(B$4:B4,Download!D:D)=0)/(Download!F:F=B4),1)),"") |
| C4,C10 | C4 | =IFERROR(INDEX(Download!D:D,AGGREGATE(15,6,ROW(Download!D:D)/(COUNTIF(B$4:B4,Download!D:D)=0)/(Download!F:F=B4),2)),"") |
| C5:C6,C11:C12 | C5 | =IFERROR(INDEX(Download!D:D,AGGREGATE(15,6,ROW(Download!D:D)/(COUNTIF(B$3:B5,Download!D:D)=0)/(Download!F:F=B5),2)),"") |
| D7 | D7 | =IFERROR(INDEX(Download!J:J,AGGREGATE(15,6,ROW(Download!J:J)/(COUNTIF(#REF!,Download!D:D)=0)/(Download!F:F=#REF!),1)),"") |
| F4:F6 | F4 | =VLOOKUP(D4,Download!J:Q,8,FALSE) |
| F7 | F7 | =VLOOKUP(D13,Download!J:Q,8,FALSE) |
| E4:E6 | E4 | =COUNTIFS(Download!$F$1:$F$9999,B4,Download!$B$1:$B$9999,A4,Download!$J$1:$J$9999,D4) |
| E7 | E7 | =COUNTIFS(Download!$F$1:$F$9999,B6,Download!$B$1:$B$9999,A6,Download!$J$1:$J$9999,D13) |
| E8 | E8 | =SUM(E4:E7) |
| G4:G7,G10:G13 | G4 | =F4*E4 |
| G8,G14 | G8 | =SUM(G4:G6) |
| D11:D12 | D11 | =IFERROR(INDEX(Download!J:J,AGGREGATE(15,6,ROW(Download!J:J)/(COUNTIF(B$4:D10,Download!D:D)=0)/(Download!F:F=B11),1)),"") |
| D13 | D13 | =IFERROR(INDEX(Download!J:J,AGGREGATE(15,6,ROW(Download!J:J)/(COUNTIF(B$4:D6,Download!D:D)=0)/(Download!F:F=B4),1)),"") |
