Hi folks,
I've got a (reasonably complicated) issue that I thought I had solved by learning how to use the INDIRECT function, but now I'm being told that my solution is inadequate because people don't like being asked to save changes when they quit if they know they didn't make any changes. Management, right? Anyway, here's the framing of my problem:
Each day, new data is gathered and put into this Excel sheet. Multiple data points are gathered each day at various values for each of my two predictor variables, so column A (number of days since a particular event) has a lot of repeated numbers in it. For example, there might be 25 different data points that were gathered on day 3, followed by another 18 data points gathered on day 4, and so on. I need a way to count how many repeated trials there are on a "per day" basis. My solution, of which I was quite proud, was to use this function to make column AF (it's a big data sheet) a "row counter" that changed its value when and only when the day in column A changed:
=IF(A4<>A3,ROW(),AF3)
Then, I was able to copy this function down the entirety of column AE:
=A3&" "&I3&" "&L3&" "&COUNTIFS(INDIRECT("R"&AF3&"C9",FALSE):I3,I3,INDIRECT("R"&AF3&"C12",FALSE):L3,L3)
My result is a concatenated column that has my Day (column A), first variable setting (column I), second variable setting (column L) and a count that starts with the first entry of each new day for how many repetitions of a given I, L combination have occurred in that day.
I need this concatenated column. I've been playing around with INDEX, MATCH, and ADDRESS, and I really feel like there should be something in there that can accomplish the same task without using INDIRECT or another volatile, "do you want to save changes" function. But I can't piece things together in a satisfactory way, so I'm turning to the larger internet community for guidance.
And, just to save you coders some time, I already tried a VBA macro. Management didn't like that either.
Any help is much appreciated!
I've got a (reasonably complicated) issue that I thought I had solved by learning how to use the INDIRECT function, but now I'm being told that my solution is inadequate because people don't like being asked to save changes when they quit if they know they didn't make any changes. Management, right? Anyway, here's the framing of my problem:
Each day, new data is gathered and put into this Excel sheet. Multiple data points are gathered each day at various values for each of my two predictor variables, so column A (number of days since a particular event) has a lot of repeated numbers in it. For example, there might be 25 different data points that were gathered on day 3, followed by another 18 data points gathered on day 4, and so on. I need a way to count how many repeated trials there are on a "per day" basis. My solution, of which I was quite proud, was to use this function to make column AF (it's a big data sheet) a "row counter" that changed its value when and only when the day in column A changed:
=IF(A4<>A3,ROW(),AF3)
Then, I was able to copy this function down the entirety of column AE:
=A3&" "&I3&" "&L3&" "&COUNTIFS(INDIRECT("R"&AF3&"C9",FALSE):I3,I3,INDIRECT("R"&AF3&"C12",FALSE):L3,L3)
My result is a concatenated column that has my Day (column A), first variable setting (column I), second variable setting (column L) and a count that starts with the first entry of each new day for how many repetitions of a given I, L combination have occurred in that day.
I need this concatenated column. I've been playing around with INDEX, MATCH, and ADDRESS, and I really feel like there should be something in there that can accomplish the same task without using INDIRECT or another volatile, "do you want to save changes" function. But I can't piece things together in a satisfactory way, so I'm turning to the larger internet community for guidance.
And, just to save you coders some time, I already tried a VBA macro. Management didn't like that either.
Any help is much appreciated!
