Cell Matching Without Using Volatile Functions

[ChrisRRR]

I have a very big WS that I am trying to speed up by getting rid of volatile functions like Indirect. Occasionally the spread sheet changes which makes things even more difficult because of Indirect.

Column A numbers 1-24 and repeats like hours on a clock.
Column C represents a 24 hour period per cell. C1 represents a time from the first 24 hour block in Column A.
Column B will say "Pin" according to the time in Column A.

The first 24 hour period, 9 was chosen and matched to Column A, hence the Pin in Column B.
The second 24 hour period, 3 was chosen and so on.

My question is how do I make a formula that is not volatile (No Indirect or Offset) that I can copy down this long sheet, where I can make C1 applicable to the first 24 hours and then make C2 for the next, and so on?

I have been struggling all day. I know I can use the row number for each selected time in Col C to represent a 24 hour block, I just cannot figure it out without using something volatile.

This formula is used to make the list in Col C.
=IFERROR(INDEX($A$1:$A$24,MATCH(Sheet1!A4,Sheet2!$A$1:$A$24,0)),"")

This is what I am currently playing with in Col B
=IFERROR(IF(MATCH(C1,A1,0),"Pin"),"")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Pin[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Pin[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[Marcelo Branco]

Maybe with a helper column (column D in the example below)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

1​

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

9​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

2​

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

3​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

3​

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

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

4​

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

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

5​

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

14​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

6​

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

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

7​

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

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

8​

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

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

9​

[/td][td]

Pin​

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

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

10​

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

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

11​

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

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

12​

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

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

13​

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

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

14​

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

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

15​

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

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

16​

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

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

17​

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

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td]

18​

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

[tr][td="bgcolor: #DCE6F1"]

19​

[/td][td]

19​

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

[tr][td="bgcolor: #DCE6F1"]

20​

[/td][td]

20​

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

[tr][td="bgcolor: #DCE6F1"]

21​

[/td][td]

21​

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

[tr][td="bgcolor: #DCE6F1"]

22​

[/td][td]

22​

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

[tr][td="bgcolor: #DCE6F1"]

23​

[/td][td]

23​

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

[tr][td="bgcolor: #DCE6F1"]

24​

[/td][td]

24​

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

[tr][td="bgcolor: #DCE6F1"]

25​

[/td][td]

1​

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

[tr][td="bgcolor: #DCE6F1"]

26​

[/td][td]

2​

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

[tr][td="bgcolor: #DCE6F1"]

27​

[/td][td]

3​

[/td][td]

Pin​

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

[tr][td="bgcolor: #DCE6F1"]

28​

[/td][td]

4​

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

[tr][td="bgcolor: #DCE6F1"]

29​

[/td][td]

5​

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

[tr][td="bgcolor: #DCE6F1"]

30​

[/td][td]

6​

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

[tr][td="bgcolor: #DCE6F1"]

31​

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

Helper
Formula in D1 copied down
=IF(ISNUMBER(C1),COUNT(C$1:C1),"")

Formula in B1 copied down
=IF(INDEX(C$1:C$1000,MATCH(INT((ROWS(B$1:B1)-1)/24)+1,D$1:D$1000,0))=A1,"Pin","")

Hope this helps

M.

 

[ChrisRRR]

Maybe with a helper column (column D in the example below)




Helper
Formula in D1 copied down
=IF(ISNUMBER(C1),COUNT(C$1:C1),"")

Formula in B1 copied down
=IF(INDEX(C$1:C$1000,MATCH(INT((ROWS(B$1:B1)-1)/24)+1,D$1:D$1000,0))=A1,"Pin","")

Hope this helps

M.

Thank you so much for that. I have no idea how you guys on here have the imagination to nest so many formulas like that. I wish it was much clearer for me. That was clever. Simple for many of you, but impressive to me.

I knew the answer would lie in 24 row segments like you have in the formula. I just could not figure out how to do it or find a thread that helped.

Thanks again

 

[Rick Rothstein]

Maybe with a helper column (column D in the example below)

If I understand the OP's question correctly, then I think this can be done without the need for a help column as well. Assuming the same layout you show in Message #2 , put this formula in cell C1 and copy it down for as many 24-hour segments as there are in total (or beyond if there might be future data added at a later date)...

=IFERROR(MATCH("*",INDEX(B:B,24*ROWS(C$1:C1)-23):INDEX(B:B,24*ROWS(C$1:C1)),0),"")

 

[ChrisRRR]

If I understand the OP's question correctly, then I think this can be done without the need for a help column as well. Assuming the same layout you show in Message #2 , put this formula in cell C1 and copy it down for as many 24-hour segments as there are in total (or beyond if there might be future data added at a later date)...

=IFERROR(MATCH("*",INDEX(B:B,24*ROWS(C$1:C1)-23):INDEX(B:B,24*ROWS(C$1:C1)),0),"")

Thank you for taking a stab at this. I tried to throw it in there to no avail. I'm somewhat of a novice at this. I wasn't completely sure how I should tweak the sheet to make it work.

Essentially, I would like Col B to say "Pin" once every 24 hours (Col A) based on the hour selected in Col C (If I select an hour that is).

 

[Marcelo Branco]

I knew the answer would lie in 24 row segments like you have in the formula. I just could not figure out how to do it or find a thread that helped.

Have you tried my suggestion above?

M.

 

[Rick Rothstein]

Thank you for taking a stab at this. I tried to throw it in there to no avail. I'm somewhat of a novice at this. I wasn't completely sure how I should tweak the sheet to make it work.

Essentially, I would like Col B to say "Pin" once every 24 hours (Col A) based on the hour selected in Col C (If I select an hour that is).

Ohhh, I had it backwards... I thought that Pin was being placed in Column B and you wanted Column C to report the hour associated with it. Now I see you want it the other way around. Just to make sure I have this clear... you will enter a number in Column C where C1 represents the first 24 rows, C2 represents the second 24 rows, etc. and you want the word Pin to appear in Column B within the appropriate 24 row range next to the number in Column A that matches the value in Column C. If that is correct, then all you need is this formula in cell B1 copied down to the last filled row in Column A...

=IF(INDEX(C:C,INT(1+(ROWS(C$1:C1)-1)/24))=A1,"Pin","")

This formula assumes your numbers in Column A start in cell A1. Also, this formula does not require a helper column.

 

[ChrisRRR]

Have you tried my suggestion above?

M.

I did. It is working to a degree.

It is hard to put my finger on it. I haven't been able to explore completely.

One issue I've noticed so far:
I pick a time in C1, C7, C14, C21 all one week apart. I expect a "Pin" entry to be in Col B for the 1st, 7th, 14th, and 21st 24 hour sets, but they are in the 1st, 2nd, 3rd, and 4th.

They are in the proper time rows that match with Col A. The formula does not skip the days that I do not select a time. It simply looks for the next available time and matches.

 

[ChrisRRR]

Ohhh, I had it backwards... I thought that Pin was being placed in Column B and you wanted Column C to report the hour associated with it. Now I see you want it the other way around. Just to make sure I have this clear... you will enter a number in Column C where C1 represents the first 24 rows, C2 represents the second 24 rows, etc. and you want the word Pin to appear in Column B within the appropriate 24 row range next to the number in Column A that matches the value in Column C. If that is correct, then all you need is this formula in cell B1 copied down to the last filled row in Column A...

=IF(INDEX(C:C,INT(1+(ROWS(C$1:C1)-1)/24))=A1,"Pin","")

This formula assumes your numbers in Column A start in cell A1. Also, this formula does not require a helper column.

I will give it a try in the morning and let you know how it goes.

I really appreciate the help. It is very satisfying making progress

 

[Marcelo Branco]

One issue I've noticed so far:
I pick a time in C1, C7, C14, C21 all one week apart. I expect a "Pin" entry to be in Col B for the 1st, 7th, 14th, and 21st 24 hour sets, but they are in the 1st, 2nd, 3rd, and 4th.

They are in the proper time rows that match with Col A. The formula does not skip the days that I do not select a time. It simply looks for the next available time and matches.

I'm confused - i don't understand what you mean by "all one week apart".
I thought the first value in column C corresponded with the first set of 24 hours (first day); the second value with the second set of 24 hours (second day); the third with the third set (day) and so on.
Now you are talking about weeks (???) - I'm afraid i don't understand what you are trying to do.
Another issue: why are there blank cells in column C?

M.