Find first entry, then second, then next etc from data

[Ally72]

Hi

I hope I can explain this properly. I did a search but it's hard to exactly how to search!

Currently using: Windows 10, Excel 2016

I have a s/sheet looking at a five year replacement plan for their PCs.
I have a list of Locations (Labs) in a uni that contains the number of machines, how much it will cost to replace and a year that (1-5).

On the right I have the years. Apart from Year 1, which is the one I'm experimenting with, they were populated manually. But if they decide they want to change, for example Lab B from the first year to the 3rd, it then needs to be done manually. What I'd like to be able to do, is, if the year is changed in column E, then the boxes / fields to the right get updated. I know I can do this with multiple pivot tables, but know that it could also be done with formulas.

In G5 for Year 1, I have used the Index and Match functions to return Lab B. I'm guessing I might need OFFSET, (which I don't know much about as yet) but before I went ahead and researched it all, thought I would ask.

Current formula (in G5) here is:
=INDEX($B:$B,MATCH(1,$E:$E,0))

I'm currently having trouble adding a screenshot - am working on it!

I'd be grateful for any help at all.

Many thanks

Ally

 

[Ally72]

I've tried the MrExcel HTML plug in to do a screenshot, but I get a "Compile error in hidden module"

I also tried the Jeanie one, but really can't see how it works. I'm at a loss as to how to do this. I'll try and replicate below:

List of locations, machines, cost to replace and year (1-5) to be replaced in.

Cell B3 onwards C3 onwards etc
[TABLE="width: 545"]
<tbody>[TR]
[TD]Location[/TD]
[TD]# Machines[/TD]
[TD]Cost[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]Lab A[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]£27,500.00[/TD]
[TD] 4[/TD]
[/TR]
[TR]
[TD]Lab B[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]£19,800.00[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]Lab C[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]£45,100.00[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]Lab D[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]£19,800.00[/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD]Lab E[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]£19,800.00[/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD]Lab F[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]£84,700.00[/TD]
[TD] 5[/TD]
[/TR]
[TR]
[TD]Lab G[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]£29,700.00[/TD]
[TD] 2[/TD]
[/TR]
[TR]
[TD]Lab H[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]£22,000.00[/TD]
[TD] 2[/TD]
[/TR]
[TR]
[TD]Lab I[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]£19,800.00[/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD]Lab J[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]£116,600.00[/TD]
[TD] 5[/TD]
[/TR]
[TR]
[TD]Lab K[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]£41,800.00[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]Lab M[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]£60,500.00[/TD]
[TD] 2[/TD]
[/TR]
[TR]
[TD]Lab N[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]£44,000.00[/TD]
[TD] 4[/TD]
[/TR]
</tbody>[/TABLE]

Example Year 2
[TABLE="width: 303"]
<tbody>[TR]
[TD]Lab G[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]£29,700.00[/TD]
[/TR]
[TR]
[TD]Lab H[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]£22,000.00[/TD]
[/TR]
[TR]
[TD]Lab M[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]£60,500.00[/TD]
[/TR]
[TR]
[TD]Lab D[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]£19,800.00[/TD]
[/TR]
</tbody>[/TABLE]



Year 1, I currently have:
=INDEX($B:$B,MATCH(1,$E:$E,0)) ... copied across

which returns
Lab B... 18 .... £19,800.00

which is great.

But I now need to work out how to find the next entry with number 1 in the Year column, which would be:
Lab C ... 41 ... £45,100.00



Does that make sense?

Thank you in advance

 

[Ally72]

I have found a resolution to this problem via somewhere else. If anyone else comes across the same problem, it was using OFFSET.

 

[wideboydixon]

OFFSET is volatile. The usual solution is something like this:

Excel 2016 (Windows) 32 bit
	A	B	C	D
1	Location	#Machines	Cost	Year
2	Lab A	25	£27,500.00	4
3	Lab B	18	£19,800.00	1
4	Lab C	41	£45,100.00	1
5	Lab D	18	£19,800.00	3
6	Lab E	18	£19,800.00	3
7	Lab F	77	£84,700.00	5
8	Lab G	27	£29,700.00	2
9	Lab H	20	£22,000.00	2
10	Lab I	18	£19,800.00	3
11	Lab J	106	£116,600.00	5
12	Lab K	38	£41,800.00	1
13	Lab M	55	£60,500.00	2
14	Lab N	40	£44,000.00	4
15
16	Year:	2
17	Lab G	27	£29,700.00
18	Lab H	20	£22,000.00
19	Lab M	55	£60,500.00
Sheet2

Cell Formulas
Range	Formula
A17	{=IFERROR(INDEX(A$1:A$14,SMALL(IF($D$2:$D$14=$B$16,ROW($D$2:$D$14)),ROW($A1))),"")}
B17	{=IFERROR(INDEX(B$1:B$14,SMALL(IF($D$2:$D$14=$B$16,ROW($D$2:$D$14)),ROW($A1))),"")}
C17	{=IFERROR(INDEX(C$1:C$14,SMALL(IF($D$2:$D$14=$B$16,ROW($D$2:$D$14)),ROW($A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Ally72]

Ah, fab - thank you. I did wonder if an array would fix it as well.

BTW: Does anyone know what html converters would work with Win 10. I tried 2 and had no luck.

 

[Aladin Akyurek]

@wideboydixon

Enter 1 or more rows in front of the first data row. Does the set up still calculate correctly?

 

[Ally72]

@wideboydixon

Enter 1 or more rows in front of the first data row. Does the set up still calculate correctly?

I've done it and it seems to be okay with me adding rows above and a column to the left (I initially removed them as I couldn't get the formula to work). The only thing at the moment, is it takes a minute to recalculate every time I add a column or row as the arrays are slowing it right down. Otherwise, it works a treat.

 

[Aladin Akyurek]

I've done it and it seems to be okay with me adding rows above and a column to the left (I initially removed them as I couldn't get the formula to work). The only thing at the moment, is it takes a minute to recalculate every time I add a column or row as the arrays are slowing it right down. Otherwise, it works a treat.

I don't think WBD would concur with you...

What follows should be a tad faster, i.e. better for performance.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]Location[/td][td]#Machines[/td][td]Cost[/td][td]Year[/td][/tr]
[tr][td]

2​

[/td][td]Lab A[/td][td]

25​

[/td][td]

27,500.00​

[/td][td]

4​

[/td][/tr]
[tr][td]

3​

[/td][td]Lab B[/td][td]

18​

[/td][td]

19,800.00​

[/td][td]

1​

[/td][/tr]
[tr][td]

4​

[/td][td]Lab C[/td][td]

41​

[/td][td]

45,100.00​

[/td][td]

1​

[/td][/tr]
[tr][td]

5​

[/td][td]Lab D[/td][td]

18​

[/td][td]

19,800.00​

[/td][td]

3​

[/td][/tr]
[tr][td]

6​

[/td][td]Lab E[/td][td]

18​

[/td][td]

19,800.00​

[/td][td]

3​

[/td][/tr]
[tr][td]

7​

[/td][td]Lab F[/td][td]

77​

[/td][td]

84,700.00​

[/td][td]

5​

[/td][/tr]
[tr][td]

8​

[/td][td]Lab G[/td][td]

27​

[/td][td]

29,700.00​

[/td][td]

2​

[/td][/tr]
[tr][td]

9​

[/td][td]Lab H[/td][td]

20​

[/td][td]

22,000.00​

[/td][td]

2​

[/td][/tr]
[tr][td]

10​

[/td][td]Lab I[/td][td]

18​

[/td][td]

19,800.00​

[/td][td]

3​

[/td][/tr]
[tr][td]

11​

[/td][td]Lab J[/td][td]

106​

[/td][td]

116,600.00​

[/td][td]

5​

[/td][/tr]
[tr][td]

12​

[/td][td]Lab K[/td][td]

38​

[/td][td]

41,800.00​

[/td][td]

1​

[/td][/tr]
[tr][td]

13​

[/td][td]Lab M[/td][td]

55​

[/td][td]

60,500.00​

[/td][td]

2​

[/td][/tr]
[tr][td]

14​

[/td][td]Lab N[/td][td]

40​

[/td][td]

44,000.00​

[/td][td]

4​

[/td][/tr]
[tr][td]

15​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]Year:[/td][td]

2​

[/td][td][/td][td]

3​

[/td][/tr]
[tr][td]

17​

[/td][td]Lab G[/td][td]

27​

[/td][td]

29700​

[/td][td]

7​

[/td][/tr]
[tr][td]

18​

[/td][td]Lab H[/td][td]

20​

[/td][td]

22000​

[/td][td]

8​

[/td][/tr]
[tr][td]

19​

[/td][td]Lab M[/td][td]

55​

[/td][td]

60500​

[/td][td]

12​

[/td][/tr]
[/table]

In D16 just enter:

=COUNTIFS($D$2:$D$14,B16)

In A17 just enter, copy across to C17, and down:

=IF($D17="","",INDEX(A$2:A$14,$D17))

In D17 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$17:D17)>$D$16,"",SMALL(IF($D$2:$D$14=$B$16,ROW($D$2:$D$14)-ROW($D$2)+1),ROWS($D$17:D17)))

 

[Ally72]

I don't think WBD would concur with you...

WBD? Sorry, not sure what you mean?

 

[wideboydixon]

He means me

WBD

You can go with Aladin's solution. You could also use the following in A17 and copy across and down:

{=IFERROR(INDEX(A$2:A$14,SMALL(IF($D$2:$D$14=$B$16,ROW($D$2:$D$14)-ROW($D$1)),ROWS($A$1:$A1))),"")}

WBD