So I understand that countif would give me a total # of a value in a set range. But what I need to do is take the inputed total # and populate the range with the value. Does anyone know a way to do that?
So I understand that countif would give me a total # of a value in a set range. But what I need to do is take the inputed total # and populate the range with the value. Does anyone know a way to do that?
Hi sixsteps,
As per me formula would not be helpful here as housing cells need to be ready with formula which is not feasible... suggest you to go for macros (vba).
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
A great question!
Say the original problem was to count the number of "Apples" in A1 thru A100 and our formula was:
=COUNTIF(A1:A100,"Apples")
to reverse this in B1 enter 17 and in B2 enter Apples
Then in A1 enter:
=$B$2
In A2 enter:
=IF(COUNTIF($A$1:A1,$B$2)>=$B$1,"",$B$2)
Copy A2 down thru A100
Gary's Student
Perhaps the FITNUOC function?
nice one Pepe
Regards,
DILIPandey
<click on below * if this helps>
@ Jakobshavn
I see what you're saying and I think that makes sense for what I'm needing to do. Can you take a look at this and help me further? I've imported the sheet on google drive, here is the link:
https://docs.google.com/spreadsheet/...oTlNJd1E#gid=0
What I'm needing to do is populate I4:33 with the data from table array D:35,E:39.
Does that make sense?
I'm not familiar with this function. Can you explain for me?
I see what you're saying and I think that makes sense for what I'm needing to do. Can you take a look at this and help me further? I've imported the sheet on google drive, here is the link:
https://docs.google.com/spreadsheet/...oTlNJd1E#gid=0
What I'm needing to do is populate I4:33 with the data from table array D:35,E:39.
Does that make sense?
How would you do a Macro DILIPandey?
May be I would follow the logic as - macro will ask you a range / column / rows etc and will input your desired value X number of times in that area.
- where x is your desired count.
For example, you'll say range = A1:B10, desired value = "abc" , X = 7, now macro will populate "abc" 7 times in the range a1:b10.
Regards,
DILIPandey
<click on below * if this helps>
Thanks DILIPandy, I just sent you an email btw!
Please post under this thread only
Regards,
DILIPandey
<click on below * if this helps>
Ok so I've been playing around with your suggestions. I have no experience with macros, so I've began to research that some.
With Jakobshavn formula above, this is great for one value, but I have multiple values with multiple quantities. So my table looks something like this
Apples 5
Grapes 5
Bananas 10
Pears 10
I need for those values to populate a column, in rows 1-30.
If you post your workbook, we can make specific suggestions.
Apologies for my ignorance, how do I do that?
When you reply, click Go Advanced, then click the PaperClip, then Select the file, then Upload the file, then touch Done, then touch Submit.
Last edited by Jakobshavn; 05-14-2013 at 11:04 AM.
Great thanks so much! sheet is now attached. I'm needing the data in the small table D35:E39 to populate the column I4:I33.
You can do it more easily with a helper column. Put this formula in F36
=SUM(E$35:E35)
and copy down to F39
then this formula in I4 copied down
=LOOKUP(H4-1,F$36:F$39,D$36:D$39)
....or without a helper column use this version in I4
=LOOKUP(H4-1,SUBTOTAL(9,OFFSET(E$35,0,0,ROW(E$36:E$39)-ROW(E$36)+1)),D$36:D$39)
Last edited by daddylonglegs; 05-14-2013 at 11:20 AM.
Audere est facere
Now that you have posted the workbook, it is obvious that my simple approach will not meet your needs. My approach will fill DTX, but it does not "know" when to switchover to RTC. Because the number of rows of data associated with DTX will change as cell E36 changes, a macro may be the best approach.
Perhaps one of the other Responders can help us.
Many thanks this is beautiful! I added and tweaked just a bit to return a NS (no service) for the situations when there are not 30 days of service.
Thank you guys! All of you!
In case you need it, see the attached:
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks