Find nonempty cell above in column and increase number

[davegscott]

Hi,

I have a macro that currently states that if there is a value in column C and a value in column S, then enter a value in column D. The value in column C is just an increasing identifier that populates based on another macro. Column S has generic data keyed by a user. What I want to happen is for the value in column D to populate as 1 and only increase when the value in column S changes. For example:

Column C has an identifier in rows 8-12, and 16-20. If column S has "SHIRT" for rows 8-18 and "PANT" for rows 19-20. Column D should be "1" for rows 8-12, 16-18, and "2" for rows 19-20.

I have the macro almost there but I don't know how to account for the varying gaps in data. Basically, column D only needs to populate if Column C has a value, but it needs to reference column S to determine if the counter increases by 1 or not. Any help is greatly appreciated!!

Please Login or Register  to view this content.

[davegscott]

To further clarify, if rows 13-15 had a third value, e.g. "HAT", I would still need the same result above. I had deleted some code prior to posting this that said in the With...End With section that if the value in column S for the next row is the same, stay the same but if it changes, increase by 1. This doesn't resolve the isssue though

[Oredigger90]

A relatively simple solution would be to have an Exit condition. I believe using

Please Login or Register  to view this content.

or add another if/else that encompasses most of the other nested if/else loops that says If IsEmpty(Cells(dummy, dummy).Value Then "whateveryourcountervariableis" = "variable" +1

And put that inside of your For Loop. A quick Google search of How to Exit a For Loop will help you with the proper syntax for your exact code, as it is hard to debug without physically seeing the initial and final values desired.

Let me know if that helps!

[davegscott]

Hi,

So I tried to incorporate an Exit condition as you mentioned, but the way I wrote it, it just stopped putting a value in column D once it found a blank value in Column C. It didn't keep going with the next row that has a value in column C. I'm kind of a novice, sorry. I am attaching my workbook for more clarification. I'm working with the GroupSortOrder macro. In my spreadsheet, I have it to the point where cells populate when they should, and are blank when they should be, but in column D, the number starts to increase before I want it to. In this example, when it says Baroque in column S it should be 1, then anything with Iconic Quilting should have a 2, etc, etc. But these values are not static and so I can't just define it to put a 1 for a specific value and so on. So for example in cell D174, when the macro gets here, I want it to look in column S and if that value matches the last column S with a corresponding value in Column D, in this case, S172, then it should populate with a 2. Otherwise, my counter should tick up one to a 3, For example when D170 looks at D145 but their correspong values in column S don't match it moves up to 2. Is this clear at all? Thanks for the help! I really appreciate it.

[Oredigger90]

Yes I think I get what you're aiming for, I've done a similar program a week ago. Unfortunately its on my work computer, so I'll check it out in the morning and try to see if I can lend a hand.

[davegscott]

Thanks so much! Let me know if there is any other info I can provide to help me find a solution.

[davegscott]

Any luck? Thanks for your help

[Oredigger90]

Sorry got side tracked. I'll send you my file of what I did that's kind of similar so you can look over it while I try to think of how yours should be accomplished. If you could make like a 20-30 row spreadsheet with example starting data and finishing data so I can better visualize how to get it done that'd be great.

Please Login or Register  to view this content.

[davegscott]

Here is a Before and After sample of what I want it to look like. I'll take a look at what you were able to provide when I get a chance. Thanks!

[Oredigger90]

So if it HAS to be 1,2,3,4,5,6,7,etc, then this code won't work. If you are fine from skipping from 2-4, or say 5-7, then this code will work. I can play around with it to get it to be incremental, but it's all on what you need. Here is my code for your sortordersample

Please Login or Register  to view this content.

[davegscott]

I need it to be 1,2,3,4,5,6,7 etc, unfortunately. Additionally, the actual file is pretty big and I want to avoid defining an else statement for each value as there are a ton, and they will change in a few months. I put HAT in there to make it more complicated because in reality my file is complicated, but, like with Hat, when something is in the middle of another group like the Shirt group, there is not a value in column C and therefore it can be skipped. Basically I was hoping there would be a way to say that the first row is 1, and if the next row that has a value in column C has the same value in column S as row 1, then it also gets a 1, otherwise increase my counter by 1 to 2. Then the next row, (if different) would be given that 2 and it would look at the next row with a value in C, etc etc. The values 1,2,3,4,5 will be in order in the end, its just a matter of determining when the row gets a number and whether the counter needs to be increased.

[Oredigger90]

Ok that's what I was afraid of lol. I think I will be able to do that, but it'll take me a little time to think the logic over in my head. I'll give it another crack though. I'll keep you posted.

[davegscott]

Sorry! You are the best! haha thanks so much

[Oredigger90]

Thank me now, this code was pretty challenging to write, but I'm proud of it.

Please Login or Register  to view this content.

So a couple of minor issues with it. Make sure you start in row 8. If your computer crashes, there is an easy fix for this, but your program will run much slower. I have a crappy computer so if it crashes with a lot of data just let me know and I'll modify it with making it wait 1 second every iteration, it will go much slower but won't crash your comp. So basically, if you have any questions on using it send me a private message or reply to this post again. But this works perfectly with your example data.

Also let me know if it works, and if so click on my star in the bottom left corner of my posts, and mark this thread as [SOLVED]

[davegscott]

Ok, it is so close to getting what I want and I am sorry its probably more my fault that its not working perfectly, THANKS so much its almost there. I have attached my results and if you filter, you can see its not a 1 number per value in S ratio. I think I made a mistake in telling you that the groups I want are in order, however, and that there are some in there that are out of order. Sadly I can't control this because I am not the sole owner of the file and was misinformed.

Basically, each value in column S rightfully is only associated with one number. However, the macro also (rightfully) gives e.g. the number 6 to the value MULTI BICOLOR when it pops up again in row 705 even though the last value was 10, which is great, I do want it to use an old number if its the same S value, but then it doesn't jump back to the next number of 11, it only goes to 7, so then there are multiple values in S associated with the number 7. This then happens again a few more times. Think you can fix it?

[Oredigger90]

No its fine, this is how programming works lol. A lot of trial and error. I won't have time today to get to it, but tomorrow I'll have plenty of time to work on it. I'm essentially trying to beef up my programming skills as I don't have anything to do at work until I leave for my Trip on Sunday. it should be an easy fix, I'll just create a new variable "p" that records the highest k value recorded, and then the "new" k will be k = p+1. If you understand what I'm saying you might be able to make a little If/Then around that portion by yourself, but regardless I'll finish it up tomorrow. But I get what you are saying on how they'll get out of numerical increasing order and such.

[davegscott]

Not a problem, that makes sense to me. Thanks so much for your help. If you are able to try to get to it in the morning, I am leaving for a wedding and will only be around til about 1230, but if not dont worry about it, the afternoon is fine. My vba skills are pretty basic and completely self taught so I appreciate what you are doing, its helping me learn a ton. I am also going to try to work on this myself this afternoon if I can to help my knowledge, but I've just been in a meeting all day. Thanks!!

[davegscott]

1 additional thing to note:

I'm going to be calling this macro from a BeforeSave event, and I want it to resort even if there is a value in column D. I think you might have it set to move to the next row if there is already a value in column D but I don't want to do that. e.g.

Baroque is 1
Iconic Quilting is 2

the user inserts a new row between these two with a new value: BICOLOR

Save file
Baroque is 1
Bicolor is 2
Iconic Quilting is 3

Hope that makes sense

[Oredigger90]

This should be the final version I believe. The MULTIBICOLOR counting issue is resolved. The Only Issue will be, all cells in column D must be blank before the sorter runs, hopefully that isn't an issue for you. I could just have the program clear Range("D8:D") before it runs the complete program. To write a different program without forcing column D to be blank would be a lot larger and time consuming to run than just clearing out column D before it is ran/saved. Here it is.

Please Login or Register  to view this content.

Edit: Also, It won't skip a row unless the value in column C is empty; if column D is empty, it just kicks it to another part of the program to increase the counter if there are no known matches for Column S so far.

[davegscott]

Works like a charm!! I was able to add in the clear column contents myself, which I did need it to do. Last little doozy of a request. In the attached I attempted to replicate the macro you created for other columns with slight variations. I can't figure out how to tweak it properly though.
Macro stylesort will work in a similar fashion but I need it to restart numbering for each group. So for group 1, the style sort order would be 1,2,3,4,5,etc for the different style numbers but for group 2, it should restart counting at 1, not continue up.
Macro colorsort works the same way as stylesort but would restart with every new style number. I think color is easiest because I dont really care whats in the color column, i just want every row for each style to increment up 1, because for each style, the colors should be unique.

Do you think this is feasible?? THANKS!!! I tried to get the macros started in the file.

[Oredigger90]

Yeah that is definitely feasible, I won't have a chance to work on it until Monday or so, as I am flying to Kentucky for a business trip on Sunday-Saturday. But I should have plenty of off time to get this done, it shouldn't take me more than an hour to get both of them.

For clarification,
- stylesort evaluates the data in column U, and if that changes while it remains the same value in column S, then increase that number by one. Then when there is a new value in column S, restart the counter back at 1
- colorsort evaluates the data in column W, and if that changes while it remains the same value in column U, then increase that number by one. Then when there is a new value in column U, restart the counter back at 1

Are those accurate assessments of what you want it to do? The colorsort is the most confusing to me of what you actually desire as an end result after the program is run. Also, are these going to be run after the data is filtered from column D in order, or will it just be part of the same program. Thanks

Edit: Also, why are some of the values bold in column W? Any reason for that?

[davegscott]

Hi, great questions. Sorry for the late response. You have it pretty accurate. The macros will run in the following order:

1 a macro I created that populates column C
2 group sort
3 style sort
4 color sort

Because of the order, I had originally suggested that style sort order populates/changes numbers based on column D and color sort populates/changes numbers based on column E because the column will have populated prior but if it's easier to base it on the columns you mentioned then that's fine.

Not sure about the bold it's probably nothing but I can double check tomorrow.

Thanks!!

[davegscott]

I may have over simplified that but I was going from memory. I'm going to look at the file first thing in the morning to confirm what I'm asking.

[davegscott]

The macros will run in the following order:

1 a macro I created that populates column C
2 group sort
3 style sort
4 color sort

Because of the order, I had originally suggested that style sort order populates/changes numbers based on column D and color sort populates/changes numbers based on column E because the column will have populated prior but if it's easier to base it on the columns you mentioned then that's fine.

When I stated the above, I think I was trying to oversimplify it. Basically, similar to the first macro we worked on, I only want it to populate for rows where column C is populated. Because they run in the order specified above, I wasnt sure if it was easier to have them all look to column C or just look at the column next to it. So stylesort could look to column D and Colorsort to column E, but if thats overcomplicating it, then they can all look at C.

For style sort, it will work very much the same as group sort, but with the new columns you mentioned. If its the same value in column U then it gets the same number and if its a new value, it gets a new number, but every time the group sort number increases, the count should restart to 1.

Color Sort order is similar but with a twist. I am not concerned with the actual value in the color column, e.g. everything with value "BLUE" should not all be the same number. The twist here is that, for each set of rows that have the same style number, i.e. same number populated in column E, or same value in column U, I want each row to have an increasing value in column F that restarts everytime column E/U changes. I have uploaded an example before/after to clarify. Its basically building a hierarchical numbering system in these 3 columns we are populating.

If you play around with the filters on the after tab in the attachment, you can get a feel for how it should work, I think. It may be a better explanation seeing it than trying to type it out. Let me know if you have questions.

[davegscott]

Have you had a chance to take a look? Thanks so much!

[davegscott]

Any chance you will be able to work on this today? Thanks so much for your help I really appreciate it.

[davegscott]

I was able to get it all to work. If you are interested in seeing how I updated the style/color sorts, here you go. Thanks for getting me most of the way. It was such a huge help.

Please Login or Register  to view this content.