Formula for Tagging

[clifford_ayala]

Hi All,I am manually tagging the colors (plan code) to each following corresponding date with colors already for FINAL tagging, problem is for example if i encountered a date with 2 or 3 different colors i have to choose the dominant color for its FINAL color tag. I've been doing the tagging manually and was hoping if someone could help formulate a formula to make things easier for my task as i am doing this by thousands of dates tagging them manually took huge amount of time and consumes a lot of energy haha, attached here is the screenshot of the scenario of what i am currently doing. really appreciate any help i could get. Capture.JPG

[:) Sixthsense :)]

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[clifford_ayala]

Thanks for your swift response, really appreciate this. attached herein is the sample scenario, though the actual is consisted of 1000 or even 10,000's of dates and been looking for advise on how to automate the results. sample desired outcome was provided on after sheet and a few notes from the task. really thankful as this will cut a huge time spent on manually tagging colors on each date. and also, before i was looking on =if statement, but still lacking some results on how to do this

[Jul Stev]

sorry, misunderstood...

[clifford_ayala]

sorry, misunderstood...

its okay hehe really appreciate all the replies here :D ~ on my note: *if the date is tagged as red, automatic the whole tag for that date will become red regardless of how many dominant the other colors are*

[:) Sixthsense :)]

I tried to avoid the helper column usage but unable to avoid it. So using Column-D as helper.

In C2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IF(COUNTIFS($A$2:$A$24,A2,$B$2:$B$24,"Red"),"Red",INDEX($B$2:$B$24,MATCH(1,($A$2:$A$24=A2)*($D$2:$D$24=MAX(($A$2:$A$24=$A2)*$D$2:$D$24)),0)))

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

In D2 Cell

=COUNTIFS($A$2:$A$24,A2,$B$2:$B$24,B2)

Drag both the formula’s down….

[kvsrinivasamurthy]

In C2 for 2/1 red tag is there. For 2/2 it is changed. But the Red tag in C10 for 2/4 is continuing for 2/5 also. how long it will continue.

[clifford_ayala]

I tried to avoid the helper column usage but unable to avoid it. So using Column-D as helper.

Thanks im currently testing this and one more thing i a bit confused on column-d helper. sorry for the noob question but i can't fully understand how it is working hehe sorry about that

[clifford_ayala]

In C2 for 2/1 red tag is there. For 2/2 it is changed. But the Red tag in C10 for 2/4 is continuing for 2/5 also. how long it will continue.

almost there i got same results too...here is the screenshot of my testing, btw fyi the actual file is consisted of 10,000 - 100,000's of rows Capture.JPG

[:) Sixthsense :)]

Please check the attachment file in Post # 6 and add some more data and test...

It is working as per the conditions which you stated in your file.

If anything goes wrong then please let me know with an excel file by highlighting in which area it is getting failed for fixing the issue.

[clifford_ayala]

Please check the attachment file in Post # 6 and add some more data and test...

It is working as per the conditions which you stated in your file.

If anything goes wrong then please let me know with an excel file by highlighting in which area it is getting failed for fixing the issue.

thank you very much sir for your time and effort on this, on my test i did made the CTRL+SHIFT+ENTER and i noticed the end of array changed into this }
i worked on the upper area but on waaayyy down it stopped and become (N/A) please see the attachment and highlighted it in yellow (TEST SHEET)

i was wondering if i did messed up in sequence? do the C2 Cell array formula must be copied first before the In D2 Cell ( helper ) ?

thanks for your replies :D

[:) Sixthsense :)]

For your info the Column-C formula is Array Formula which Requires CTRL+SHIFT+ENTER

But Column-D formula is Normal formula which DON'T REQUIRE CTRL+SHIFT+ENTER.

Place the cursor in C2 cell and if you see the formula I have stopped the ranges to 24 rows. Because in your previous file the data is present for 24 rows.

So the formula is referred until 24 rows. Just replace the 24 to 148 since at present you are having the data until 148 rows.

In C2 cell formula replace all 24 to 148 and enter it as Array Formula by pressing CTRL+SHIFT+ENTER.

Drag the C2 cell formula down...

Place the cursor in D2 cella and change the all 24 to 148 and drag it down...

Now it will show the right results.

Whenever your data size increase/decrese just adjust the reference in formula for getting right results.

[clifford_ayala]

wow thanks its now working XD...though my pc hangs a bit on processing almost 100++ rows hahaha...really thanks mannn ~ this really saves a lot of my time ...if i can only givee you 100000 repp LOL ..more than words thank you very much :D

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback and rep