• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy rows based on criteria

Jack-P-Winner

Jack-P-Winner

Member
Can you copy rows of letters and place the answers in another column if the letters change from row to row? I have 4 digit counters that either go Up or Down, are Even or Odd so I place a UD or EO along side them. I want to duplicate what they do in one column and place the new results in another column "except" the counter numbers will be different.
 

Attachments

  • Automate Countersv2.xlsm
    54.8 KB · Views: 17
I don't often say this, but could you perhaps provide less information? ;)
There is a lot going on in your sample worksheet, and I'm not sure what part to focus on. I have a vauge rememberance of this type sheet, when you were doing something with combinations, but not enough to understand your layout I'm afraid. :(
 
Jack-P-Winner said:
Can you copy rows of letters and place the answers in another column if the letters change from row to row? I have 4 digit counters that either go Up or Down, are Even or Odd so I place a UD or EO along side them. I want to duplicate what they do in one column and place the new results in another column "except" the counter numbers will be different.
Click to expand...
 
Hi Luke,

I am trying to figure out another way to explain this. If you look at rows 43 and 44 AC AD AE AF AG. One of the 4 digits will go up 1 number each time it advances. If the letter in AG is U or E the digit in AC or AE will go up . So if we have 1111 and there is a U or E in AG the counter will advance to either 2111 or 1121 (1st or 3rd digit). If there is a O or D in AG the counter will go to 1211 or 1112 (2nd or 4th digit) If there are two of the same letters in a row it will advance to the next field. What I mean is , if there is a U row 42 and its 1111 it will go to 2111, if there is another U row 41 it will go to 2121. They alternate one to the other. The D and O work the same way. Does this make better sense? H thru Z are examples
 
A good rule of thumb is the cells will always have an even amount of the same numbers vertically. If you look at the example you will see four 2s rows 31-34, four 3s rows 33-36, two 8's rows 32-33. If you have 3 or an odd number then the counter advanced wrong rows 35-37, rows 36-38. You can place any 4 digit numbers across any of the 4 columns like OPQR and play with the BBU and BB_U BBD BB_D to see how it goes up. Just clear entries from 30-43
 

Attachments

  • 3 vs 4 cells.jpg
    3 vs 4 cells.jpg
    178.4 KB · Views: 3
I understood your first example, but I don't see how it matches what you actually have. :confused: See the attached, range AC30:AF43 for formulas that produce like you describe in post #5.
 

Attachments

  • Automate Counters LM.xlsm
    55.2 KB · Views: 8
Ok, now that I am confused too LOL, I am so sorry for confusing you Luke and anyone else peeking in. The readers digest version....I was trying to do this from work at our busy café, our ATT internet was down so working off my iPhone (LOL) and on top of that my 7 month old grandson is cutting teeth so lets start over please with this upload
I have changed the formatting so you can press the cells in the 1st row and the counter advances all 4 digits. This is what I want to automate (example HIJK)

Let me know if this makes better sense
 

Attachments

  • Automate Counters.xlsm
    65.8 KB · Views: 5
Hi Jack,
I understand, we all have our busy moments. :)

Trying to take this simpler...here's one of your samples, and I've marked which column changed.
upload_2014-6-11_9-12-22.png

Can you help me understand the logic/progress? I see that the numbers increment, 1-9. O seems to trigger col 3 and 4, while E triggers 1 and 2. The question: how do you determine which column gets triggered? Looking at just the O's, starting at bottom, it toggled 3,4,4,3,3,4,4,4,3
I don't understand what the pattern is.

Similarly, the E's go:
1,2,1,1,2
 
Thanks Luke, I'm not sure how much longer you will be up as I am in USA and it's 8:30 am here but I can do a short video to explain? Let me know

The trigger actually comes from an input on another page that determines what they do but for this part it has no set trigger. I'm using this as a tool I am copying the pattern (UDEO) from a set pattern that ran on one of my many sheets. I am pasting it to a blank sheet and duplicating the pattern (EOUD) 20 times using different variables which are the 2 rows of 1111 2222 3333 4444 ..... When it starts on the bottom letter that's its trigger for this. The object is to start with an even amount of cells vertically. I do not have odd verticals cells A video will do wonders I think. But if you play with HIJK and go back and forth from H J I K or H J H J you will see the patterns start to form
 
I just realized my spread sheet was inaccurate as far as the conditional formatting so my apologies. This one is correct. This video has sound and hopefully someone will be able to help. I REALLY appreciate all the assistance thus far. I can have 1 button per row or 1 button to do them advance all at once. Video is on its way. Here is a corrected file with proper CF
 

Attachments

  • Automate Countersv2.xlsm
    48.4 KB · Views: 1
I am trying to duplicate what is happening in columns HIJK 30-44 in columns OPQR and VWXY. E and U are in columns HI OP and O and D are in columns JK QR. I will have 2 rows of numbers as in rows 43 and 44 OPQR and VWXY.

If 1st movement is a U or E column(s) H or I OR O or P will advance UP 1 digit from 1111 to 2111 or 1211 in OPQR or 3222 or 2322 in VWXY.

If a 1st movement is a O or a D column(s) Q or R OR X or Y will advance up 1 digit from 1111 to 1121 or 1112 in OPQR or 2222 to 2232 or 2223 in VWXY.

2nd movement UP row 41 will have to be in column(s)P if U in OPQR or V in VWXY. You can not advance up a row if you have a single cell like the 3 in O42 or the 3 in W42

3rd movement Up is an O or D so either Q or R in OPQR will go up or X or Y in VWXY. Because there is an even number of cells like 4 1's vertically in columns Q and R rows 41-44 and columns X and Y in rows 41-44 either column can advance Up. I will pick Q and Y to go Up

4th movement Up is another O or D so again it will move in Q or R OR X or Y. Because there is a single cell in Q40 and Y40 columns R and X have to go up even though there is an Odd amount of cells vertically 40-44 (OPQR VWXY) The only place it can advance Up with an od number of cells is from the starting point on the bottom rows. After it gets a foundation of even number of cells vertically it will stay even.

The next movement Up is an E or U so because there is a even number of cells vertically in columns O 39-42 and W 39-42 they are the ones that advance Up

The next one to advance is another E or U so the P or V will go Up

This is how the counters move up. The conditional formatting is very important so I need to have the green font with gray shade and red font with yellow shade

I hope this makes better sense and again. THANK YOU for your assistance
 

Attachments

  • Automate Countersv3.xlsm
    49.4 KB · Views: 4
Hi Jack,

Take a look at my attached file, please, and check:
  1. the logic of how things advance match up with your desired. I think I've got that correct.
  2. The coloring of the cells, which I based off of some ISODD and ISEVEN CF rules
  3. Layout is correct
If all 3 are correct, could you state what is next goal? Are we trying to remove macro, or does it somehow play in? I disabled the macro for now while I was working on the file, as I'm not sure what it does and didn't want to get into it yet.
 
Hi Luke,

no file attached and Macro can be disabled or deleted. I left the macro there so you can click on HIJK 1 and watch how the counters move
 
Doh! Sorry about that, re-trying.
 

Attachments

  • Automate Counters LM.xlsm
    50 KB · Views: 5
Luke,

So I do not mess you your formulas. I placed 2 rows of 3's or 4's in columns SC AD AE AF and then copied over the formulas. Is there another way I should run this? The only issues I see so far is for example rows 36 37 38 leaves an odd number of cells It should always be an even number of vertical cells

How would this work if we had the attached? I added an additional column and you will see I listed 1-2-3 and 1-2-3-4 down columns U and AA.

What happens when the pattern changes like in AJ AK AL AM? Is it possible to put 2 rows of numbers like rows 43 and 44 AJ AK AL AM and press the go button in AI and they move up on there own or should I just copy and paste new set of formulas every time?

Again! Thank you!!!!!
 
You must log in or register to reply here.
Back
Top