Cell Matching Without Using Volatile Functions

ChrisRRR

New Member
Joined
May 16, 2016
Messages
30
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]

 
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.

I don't mean to confuse. It is a little difficult to convey my thoughts on this project. I'm trying to figure it out myself, but it sounds like you understand what I'm trying to do from your explanation

C1 corresponds to A1:A24, which directly affects B1:B24
C2 to A25-A48 affects B25:B48
C3 to A49-A72 affects B49:B72

There may not necessarily be an entry in the Col C rows. There might be for one day, but not others. There is no pattern or way to know how Col C will be filled out. It depends on the user

I just made some entries into the sheet to test the functionality. The "week apart" entries were arbitrary. It was in an effort to see how Col B reacted when there were gaps in Col C entries

Lets say there is an entry in C1 and C3, but not C2. The function in Col B works, but I expect B25:B48 to remain empty as C2 is empty. What happens is that C3 is matched to A25:A48 and the respective row in B25:B48 will say "Pin"
 
Last edited:
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.

Fantastic! That worked out perfectly. I did have to make the Col C rows absolute to account for the range I'm using in the index function. It accounts for gaps in Col C entries and is spot on.

Thank you so much.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top