Transfer Results to Different Cells

Hello,

It has been some time since I have been on Excel Forum and I keep coming back because this Forum never fails me.

To my problem, I have sheet 1 and sheet 2 in my attachment. Sheet 1 i enter information manually at this time, but maybe later get some formulas for it.

I will continue at this time entering sheet 1 manually, but sheet 2 is where i need formulas in place that look at sheet 1 for information to put in sheet 2.

Column B is where the first formula will go and then starting in Column D is where the others will go. I explain this in more detail in the attachment.

I'm trying to count numbers from sheet 1 to put in column B by counting how many times it has been since that number showed up last, and then transfer that number to column D once it shows up again and have that number go back to 1 and start over.

This will be continuing pattern over time.

If you have any questions regarding what i'm trying to accomplish please dont hesitate to ask.

Thanks,
Brian

I’ve moved this out of the new user and basic forum section - it’s clearly above that level!

Thank you,
I realized what I did once I posted the thread. I didn’t know how to change it.

You couldn't have changed it - if this happens again, just contact one of us mods to adjust it for you.

I have looked at the workbook and don't understand how you arrive at the counts - where are they coming from and how exactly do they relate to the other sheet (which, by the way, contains a reference error in column B)?

Excel 2016 (Windows) 32 bit

	A	B	C
1
2	17 October 2019	#REF!	713
3	17 October 2019	#REF!	741
4	17 October 2019	#REF!	676
5	18 October 2019	#REF!	827
6	18 October 2019	#REF!	104
7	18 October 2019	#REF!	361
8	19 October 2019	#REF!	917
9	19 October 2019	#REF!	762
10	19 October 2019	#REF!	919
11	20 October 2019	#REF!	041
12	21 October 2019	#REF!	674
13	21 October 2019	#REF!	438
14	21 October 2019	#REF!	631

Sheet: Sheet 1

Al33 is blank, not zero. Please re-check ALL ASPECTS of your explanation and amend.

Sorry for the mistakes, I got in big hurry while making this post.

I have added some things on sheet 1 and sheet 2. Hopefully it will be easier to understand.

Read comments on sheet 1 first which should in turn be easier to understand sheet 2.

Thank You Ali and Glenn for responding!!

Anybody having any luck with creating formulas for my post?

I haven't even looked - sorry. Will have a peek now.

EDIT: Sorry - there's just too much going on in there for me - too much to read, too many things to find and work out. I'm not going to offer any further help on this one, as I don't have the time - really sorry.

Me too. Way too much going on. I gave up after your first post!

Close this thread and break it up into bite-sized chunks, one at a time.

Step by step, we'll get there.

Perhaps this will help.
I am going to tackle the first request (cell A1 on Sheet 2) and see how that goes before proceeding.
Place the following into cell C13: =AGGREGATE(14,6,(ROW(A3:A500)-ROW(A2))/(Sheet1!B3:B500<>""),1)
Note that you could hide this number by setting the font of cell C13 to white. If you decide to move the formula to another cell be sure to change the reference in the formula below.
Place the following into cell B15 and copy down: =IFERROR(C$13-AGGREGATE(14,6,(ROW(A$3:A$500)-ROW(A$2))/(Sheet1!C$3:AU$500<>"")/(Sheet1!C$3:AU$500=A15),1),"")
Let us know if you have any questions.

As Glenn has advised, take each stage step-by-step - start a new thread for the first stage with a simple sample workbook.

I suggest you mark this thread as solved, or I can close it for you.

Thank you JeteMc,

The first request seems to be working exactly as I described.

I really hope you can do the others as well, you're on the right track.

Ali, I'm going to hold off from starting new thread of each step until I hear back from JeteMc. I used his formula for the first step and it seems to be working as intended.

I'm hoping by him doing this first step that he can also do the others.

Thanks for staying in touch with me.

i come up with some crazy stuff in excel, but of course I have no idea how to create a formula for this kind of excel sheet, but I do know what it should do.

Without "Excel Forum" I would not even know how to start something like this and would probably just continue doing everything manually.

I love all you guys for what you do.

Thanks again,
Brian

No worries.

I feel as if the formulas in post #11 cover the request in cell H1. If I am wrong, please help me understand.
I am lost about the request in O1. It starts out talking about filling cell D15 but then it states the value should be placed in F15.
The request also deals with what would happen if another row was added. It would seem to me that what we need to address is what we should do with the present data, as that is the data we can actually see and work with.
As there is another zero in row 73 (Sheet1) it would seem that the value in cell D15 should be 19, however it is 13.
I feel that we need to know exactly why the current value in cell D15 is 13 in order to proceed.
Let us know if you have any questions.

Yes, #11 post does cover H1.
For the cell in D15 the 13 comes from "Past Data" or "History" of how many times that number counted up in B15 on sheet 2 before going back to "1" again.
If you go back to Cell I60 (sheet 1) you will see a "0" in that cell, then you start with row 61 counting as 1, row 62 as 2 and so on until you get down to the next "0"
which is in cell AK73. This comes out to 13 rows. Because the "0" counted upto 13 times B15 would then be set back to "1" as it does with your formula, but I
need the history of the B15 to be transferred over to D15. This is how the "13" arrives in cell D15 on sheet 2.

We then look at the next "0" on sheet 1 which is AK74. B15 has done what it should to do and have a 1 in it, but because the "0" showed up the "1" just like the "13" is transferred over to E15
then B15 goes back to "1" again and starts counting until the next "0" shows up. Lets say it showed up in C93 on sheet 1, this would cause B15 to transfer the number that is in B15 to the next cell in
row 15 on sheet 2 which is F15, but note when it transfers this number from B15 it will add a "1" to the number that was in B15 which makes it "19" in F15. Then if you go and look back at sheet 1
you will see that it was 19 times since the "0" showed up last (Count starting with 75 down to row 93 this comes out to 19 times.)
This pattern will continue adding more and more Data to Row 15 on sheet 2. This will allow me to see the pattern of how many times before a number like "0" shows up over time. I will have to fill the
formula from D15 out to at least Z15 for me to see this history data and also copy down for the other numbers like 1, 2, 3 etc.

I hope this helps in understanding O1

I am having a hard time understanding the logic.
To me it seems like before the zero was placed in row 74 there was a zero in row 73 so the history prior to cell AK74 (value in cell D15) would be 1.
Before the zero was placed in row 73 there was a zero in row 60 so the history prior to cell AK73 (value in E15) would be 13.
Before the zero was placed in row 60 there was a zero in row 51 so the history prior to I60 (value in F15) would be 9 and so on.
I am sure that I am missing something, however at the moment I am struggling to understand the logic of the order in which the values are placed in D15 and E15.
Perhaps someone else will be able to understand. I will look at this again later and see if I can be of more help.

You’re correct with this. You’re not missing anything. We just need to establish a start point (so we can start with the 13 in D15 or the 1 in E15)
Once the start point is established the rest will just be filling along row 15 and down. I figure it will be a formula with Index and Match, not for sure.
If we could get the number in B15 to show in D15 and then add plus 1 to D15 once the “0” shows up again this would work with D15 being the start point. After that be able to copy across from D15 for future “0” this would be the history of “0”

Let me know what you think? Maybe if I need to I can start a different thread with this issue.

You’re correct with this. You’re not missing anything.

I am going to make a proposal based on this portion of the post.
Select cell D15 and paste the following formula into the formula bar:

Formula:

Please Login or Register  to view this content.

Once the formula in activated (Enter) copy over and down.
Let us know if you have any questions.

That looks like it will work perfect, but is there a way to turn it around the other way to where future numbers will be able to copy them across?

In other words it will start with the number that is in L15 will start in D15, K15 will start at E15 and so on, J15 will start with F15 and so on.

You're definitely getting what i am trying to do and I can't thank you enough

Ah, now I think that I understand.
Same directions as post #20 with the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

JeteMc all I can say is "Great Work"

At first I thought your formula had some issues because it didn't match my original workbook, but come to find out I had made several mistakes entering in the information manually, lol.

Your Formula corrected those mistakes i had made.

A couple of questions i do have:
1. Since my original workbook does not have same cells as sheet 1 and sheet 2 that you're seeing, how do I know what number to put in the place of (15, 6) in your formula?
I have the other parts of the formula corrected to match my workbook, but I didn't know what to put at the 15, 6 in your formula.

2. Is there any suggestions you have that will speed up the calculations of these formulas? Once I added them to my workbook, It slowed things way way down on calculating.
I filled down the sheet and it has been calculating forever. I do not if it will end up freezing the workbook or it will eventually put in the data.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

how do I know what number to put in the place of (15, 6) in your formula?

Have a look here to understand those numbers: https://support.office.com/en-gb/art...6-e19993fa26df

thanks Ali, Sorry

Was the computer able to complete the calculations? I didn't notice any issues however I only filled over to column P.
If the data on sheet 1 isn't going to extend down as far as row 500 you could reduce that number in the formula.
You could also add a helper column that counts the number of times a value appears on sheet 1 and exchange IFERROR for IF as follows:

Formula:

Please Login or Register  to view this content.

Note that cell X15 contains the formula: =MAX(0,COUNTIFS(Sheet1!C$3:AU$500,A15)-1)
If your computer slows down while inputting numbers on sheet1 then you could go to the Excel options and change the formula calculation option to manual. You would then need to press the F9 key when you are ready for sheet 2 to produce new output.
If the problem persists, you may want to open a new thread, once this one is marked 'Solved' (using the thread tools menu above your first post) that addresses that issue, as contributors may come up with other methods of reducing lag.
Let us know if you have any questions.

I was not able to complete the calculations last night, but with your new suggestions it calculates pretty quick.

My data will extend past 500 in time, but the help in column X made it ok for now.

This issue getting solved for me by you has made a world of difference when adding information to my workbook.

I will show thread solved and add reputation.

Thanks again for taking the time to help me.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.