Hey guys, I have some data and would like to make a list with spacers/constants at certain intervals, determined by what is in the cells adjacent to the data. Best way to see what I want is to open the spreadsheet I think. If anyone has any ideas its much appreciated. Thanks!
List problem.xlsx
Maybe I should have posted this in the Formulas and Functions forum but I know I'll get yelled at if I post it again there.....
Do you want the break after every color (Column C)? How do I determine "Some Repeating Text?: Is it the first row? Is it all the text found until the first change in color?
The forum you posted in, is fine
How close is your sample, to your actual data?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you to both of your for your replies.
dflak, I want the break after every color, as determined by the strings in column C ("Yellow","Brown", etc.), not the cell fill color. "Some", "Repeating", and "Text" are just three cells which I want to repeat after each break.
- Likely, two of them will be empty strings and one may have some text in it. I don't mind it you reference those cells or hardcode them into the formula, I can figure it out either way.
- Assume for my purposes that it will always be exactly three cells that I want to repeat. No more, no less.
FDibbins, my sample is very close. The actual data is much longer, but it's all just text strings. Some further clarifications:
- All of the values in column B ("Banana","SchoolBus","Lemons", etc.) are unique
- The values in column C repeat often
- Ideally the three cells in column D are not necessarily unique, to accommodate blank cells. But if you can write a formula that doesn't fit that, I can just use spaces to make it work (i.e. " "," "," ")
Let me know if I can clarify anything else and thanks again!
Edit Please disregard. Posted before refresh.Originally Posted by dflak
dflak,
Yeah I scratched my head over that one, too this morning.
I think we are to understand that D5:D7 is the location of and contains the repeating text in question. From there I take it we are to inter-weave color range subsets with that range.
That said I am waiting for OP's responses. I'd also like to know the answers to Ford's question.
Dave
Do you mean "Yellow" and others will be repeated again further down the column?•The values in column C repeat often
Will the colors always be contiguous as in sample?
FlameRetired, the values repeat as much as you see them. Yellow will not be seen down below Blue, for example. So yes, column C values will always be contiguous/adjacent if they repeat.
Sorry that my original example was not clear enough. I hope this is helpful in illustrating what I am trying to accomplish.
List problem 2.xlsx
Screen Shot 2015-12-04 at 4.57.15 PM.png
Last edited by JYTS; 12-04-2015 at 06:07 PM.
Try this: a lot of hard coding in it.
Please Login or Register to view this content.
Hi dflak, I was expecting a formula of some sort. Can you walk me through how to put what you wrote to work - I'm not familiar with VBA at all.
I hope this helps:
Please Login or Register to view this content.
Yes, that definitely does I will take a close look at it. But I'm actually even dumber that that. I copy your code, the paste it..... where? Under "View code" on the sheet? Do I need to change anything in the cells to make this work?
Just give me a quick step by step on what to do after I copy your code - I'm that much of a beginner!
EDIT: Never mind I figured it out! Thanks a ton! +1
Last edited by JYTS; 12-04-2015 at 06:37 PM.
Ok just for anyone else wondering I figured out how to do it with a formula. As based on the original spreadsheet I uploaded with results in H5:H27...
Set the first cell (H5) equal to the first result. The set all the rest to
{=IF(H5=$D$7,INDEX($B$5:$B$15, MATCH(0,COUNTIF($H$4:H5, $B$5:$B$15), 0)),IF(H5=$D$6,$D$7,IF(H5=$D$5,$D$6,IF(INDEX($C$5:$C$15,MATCH(INDEX($B$5:$B$15, MATCH(0,COUNTIF($H$4:H5, $B$5:$B$15), 0)),$B$5:$B$15,0))=IFNA(INDEX($C$5:$C$15,MATCH(H5,$B$5:$B$15,0)),0),IFNA(INDEX($B$5:$B$15, MATCH(0,COUNTIF($H$4:H5, $B$5:$B$15), 0)),""),$D$5))))}
It's just the kind of convoluted unnecessary avoidance of VBA that I love!
I usually prefer a non-VBA solution myself. Even when I use VBA, I try to get "basic" Excel to do most of the heavy lifting.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks