# of events vs Day of week. Compare 2 columns, then copy # events cell

[thankyou]

Hi. I have 2 columns: B & D.
B has # events. D has day of the week expressed as a value (2-7 from Mon-Sat respectively).

I'd like to copy the values in B over to J20 thru O20 (J-O represent M-Sat as you probably expect) IF they have the same column D value (day #). Conceptually, it's just copy over each day's data into easier-to-read columns beginning J20-O20, so that I can more easily run some simple analytics. This is probably a common question, but I'm still learning so appreciate it that much more. Thanks for stopping by to help.

 

[iliace]

I'm not sure I understand why the copying is happening in row 20 (J20:O20), rather than every row. I'm also not sure that doing analytics is easier with weekdays broken out into separate column, as opposed to having a single Day # column. That said, here is a fairly simple formula that would distribute the Event # to J:O, which you can copy and paste across that range. Then you can copy/paste values if you feel so inclined. Note, some columns are hidden to adhere to your cell addressing but save space in the post.

	B	D	J	K	L	M	N	O

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Event #[/TD]
[TD="align: center"]Day #[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]4[/TD]

</tbody>

Formeln der Tabelle

Zelle Formel J2 =IF(COLUMN()-10+2=$D2, $B2, "") <tbody> </tbody>

<tbody>
</tbody>

The -10 is subtracting J's column number, and the +2 is adding back the first day's number (Monday=2).

Does this help?

 

[thankyou]

iliace, First, HUGE thanks for your post/reply. I'll definitely be checking that out and let you know the follow up (as if you need it *smiles*). Second, right, yeah sure I agree with you that generally it's easier to do analytics on a single column. However, in this case, the reason I wanted to copy those cells out onto their own days is so that I could quickly do simple one-touch calculations (eg various excel-built-in averages) and cross check my larger sheet. It's a quick way for me to "manually" double check formulas (triple check if you will since I might take a small sample and then "visually" inspect and then try excel's average formulas to ensure much more complicated formulas are working). Also, you were asking __

I'm not sure I understand why the copying is happening in row 20 (J20:O20), rather than every row.

The reason there is just so I can grab a quick sample and throw it on the same page where I happen to have space (J20-O20 and down). This is an ideal way for me to verify formulas, esp since I'm still pretty much an enthusiastic beginner 'cuz I've been learning so much lately. It's a lot of fun, thanks to great contributors like you.

I'll follow up.

Thanks,

Bob

 

[thankyou]

iliace, I'm having a little trouble, my error for no doubt.

Your table above shows that you completely get what I'm asking.

My range, for now, is B2:B40, so when I update your formula with my range, like this:

=IF(COLUMN($B2:$B40)-10+2=$C2, $B2, "")

I get a blank value.

//btw, I also updated D2 to C2 since I'd given you the wrong column. //

I'm sure I'm doing something wrong. Thanks for your guidance.

bob

 

[iliace]

Hi Bob,

COLUMN() should not have any parameters. Without arguments, it refers to the column that the function is in. Remove the B2:B40 reference.

The change from D2 to C2 should be fine. As long as the weekday numbering is still the same.

I should have been more explicit about what the formula does. =IF(COLUMN()-10+2=$D2, $B2, ""). In plain English, here is what's happening: "Take the column number of where this formula currently is (COLUMN), subtract 10 because J is the 10th column, and add back 2 (the Monday, first day). If the resulting number corresponds to the weekday number in D2, display the corresponding value from B2. Otherwise, display a blank".

Hope this helps,

-Ilia

 

[thankyou]

Ilia, Hey thanks so much for that follow up and clarification. Works beautifully

=IF(COLUMN()-10+2=$C2, $B2, "")

By the way, once the cells are copied over into J20-O20 and down, I don't want any blanks. So, output would be just like what you'd provided in your table above, but without the intervening blanks.

How can I revise that to not introduce blanks. "I want every bullet to be a live round!" haha.

Plus (if you know) it would also be cool to learn how to remove the blanks data that already has blanks. I looked at "consolidate" but that wasn't right. For others, I also found an interesting (common?) method in the following 2 articles ("special > blanks > delete ...")

Remove Empty Cells in Excel 2007 or 2010 Spreadsheets

Microsoft Excel Tips

... but since those blanks cells have a formula in them, they're not really considered blank,so that method doesn't find them. I like that type of ad hoc solution (in addition to learning how to revise the formula to do it), and am not really into VBA or macro solutions btw.

Thanks so much for your help so far!!

Bob

 

[thankyou]

...so, as I'm thinking more about it (and this is for anyone who might know) all I really would like to know at this point is, assuming formula:

=IF(COLUMN()-10+2=$C2, $B2, "") <<<<<although that could probably be any formula for this question,

how can I revise it so that blanks don't show in the resulting output? thanks so much!