Question description:
This user has given permission to use the problem statement for this
blog.
I have a file in which I need to somehow use a conditional formatting for the same names of the data available, or if there is any other way to calculate the frequency of one particular name appearing.
Solved by K. F. in 51 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
12/09/2018 - 07:37
Hello! How are you?
User
12/09/2018 - 07:38
Hello, fine thanks. How about you?
Excelchat Expert
12/09/2018 - 07:38
Is it possible to share a sample of your data on the document preview or even better, share the whole file as an attachment to the chat?
Excelchat Expert
12/09/2018 - 07:38
I'm good. Thanks for asking ;)
User
12/09/2018 - 07:38
Yes, I will share a sample here and if needed explain better what I need
Excelchat Expert
12/09/2018 - 07:39
Nice. Thanks
User
12/09/2018 - 07:40
[Uploaded an Excel file]
Excelchat Expert
12/09/2018 - 07:41
Downloading. Thanks
Excelchat Expert
12/09/2018 - 07:42
Alright. I copied the data on the document preview on the right. :)
User
12/09/2018 - 07:42
So, as u can see, there are somehow 4 tables
Excelchat Expert
12/09/2018 - 07:42
Yup.
User
12/09/2018 - 07:42
not formatted as tables but
User
12/09/2018 - 07:42
and there are names that are similar within those tables
Excelchat Expert
12/09/2018 - 07:42
Okay.
User
12/09/2018 - 07:43
and I would like to either for example format them by colors
User
12/09/2018 - 07:43
will be back
User
12/09/2018 - 07:43
in 5
Excelchat Expert
12/09/2018 - 07:43
So each name gets a different colors?
Excelchat Expert
12/09/2018 - 07:43
Sure. No worries.
User
12/09/2018 - 07:48
here I am sorry
User
12/09/2018 - 07:49
No I meant, in those 4 tables
User
12/09/2018 - 07:49
if there is one name that appears in all of them
User
12/09/2018 - 07:49
to give it the same color
User
12/09/2018 - 07:49
or idk some other way that would make it easier for me to track the same name appearing in all lists
Excelchat Expert
12/09/2018 - 07:50
Okay. So the goal is to identify names which recurs in all of the 4 tables.
User
12/09/2018 - 07:50
doesnt necesssarily have to be in 4 tables
User
12/09/2018 - 07:50
but to appear more than in 1 table more or less
Excelchat Expert
12/09/2018 - 07:50
I'm sorry - to appear more than in 1 table more or less < this got me a bit confused?
User
12/09/2018 - 07:51
exclude that more or less
User
12/09/2018 - 07:51
i meant more or less this is what i am asking
User
12/09/2018 - 07:51
to somehow keep track of the name that appears more than in one table
User
12/09/2018 - 07:51
sorry for the confusion
Excelchat Expert
12/09/2018 - 07:51
Okay, gotcha.
Excelchat Expert
12/09/2018 - 07:52
We may need more time so I'd like to kindly request for extension later on. We're allowed to extend the session up to 60 mins for free before it automatically ends ;)
Excelchat Expert
12/09/2018 - 07:52
Thanks!
User
12/09/2018 - 07:52
I think I can do even more if needed because I have a subscription here so no worries
Excelchat Expert
12/09/2018 - 07:53
I see. But please be reminded that regardless of the subscription, the session can only last up to 60 mins max. :)
Excelchat Expert
12/09/2018 - 07:54
I'll be working on a solution locally in excel now and get back to you with updates from time to time. Appreciate your patience.
User
12/09/2018 - 07:54
sure
Excelchat Expert
12/09/2018 - 07:56
Please see attached
[Uploaded an Excel file]
User
12/09/2018 - 07:57
so what did u exactly do? I was thinking something more advanced, like different colors for different names, but then same color for the same name across different tables
User
12/09/2018 - 07:57
if you know what I m ean
User
12/09/2018 - 07:57
mean*
Excelchat Expert
12/09/2018 - 07:59
Oh, that would be tough using conditional formatting. As you can see you will to create the same number of conditional formatting rules with the same count of the recurring Consigness in column B.
Excelchat Expert
12/09/2018 - 07:59
I can suggest something else. Like for example a column to tag recurring names with different assigned values.
User
12/09/2018 - 08:00
well, please go ahead and show me how. that would be nice
User
12/09/2018 - 08:00
I just said formatting because did not have any other ideas in mind
Excelchat Expert
12/09/2018 - 08:00
One moment. :)
User
12/09/2018 - 08:00
sure
Excelchat Expert
12/09/2018 - 08:00
I'll be back
User
12/09/2018 - 08:00
take your time
Excelchat Expert
12/09/2018 - 08:06
Hi! Please see attached
[Uploaded an Excel file]
Excelchat Expert
12/09/2018 - 08:06
Will your data exceed 999 rows?
User
12/09/2018 - 08:07
not at the moment
User
12/09/2018 - 08:07
haha
User
12/09/2018 - 08:07
why?
Excelchat Expert
12/09/2018 - 08:07
Okay, because you can notice in the formula in column G
Excelchat Expert
12/09/2018 - 08:07
there is a part which says TEXT(....,"000")
Excelchat Expert
12/09/2018 - 08:08
That means configures the ID to look like C_001 - C_999
User
12/09/2018 - 08:09
okay
User
12/09/2018 - 08:09
but I am not really sure how this is going to help see faster if for example C_004 is recurring in next tables?
User
12/09/2018 - 08:09
maybe I am not getting it, if you could elaborate
Excelchat Expert
12/09/2018 - 08:09
So if you use filter function, you can then filter out one specific ID generated by the formula to show the Consignee with the same ID from the different tables
User
12/09/2018 - 08:10
okay
User
12/09/2018 - 08:10
the ID
Excelchat Expert
12/09/2018 - 08:10
Yup?
User
12/09/2018 - 08:10
any specific reason u chose that kind of numbering
User
12/09/2018 - 08:10
or naming..
Excelchat Expert
12/09/2018 - 08:11
No particular reason. C is for consignee
User
12/09/2018 - 08:12
okay, I like the idea, I am just thinking if it would be more of help for me to use some other way to name them
Excelchat Expert
12/09/2018 - 08:12
Do you want me to add another column so you are able to identify from which table the consignee originated from?
User
12/09/2018 - 08:13
that would be great too
Excelchat Expert
12/09/2018 - 08:13
Okay, one moment.
Excelchat Expert
12/09/2018 - 08:15
Please see attached
[Uploaded an Excel file]
Excelchat Expert
12/09/2018 - 08:15
In this file, I filtered out C_004
Excelchat Expert
12/09/2018 - 08:16
And as you could see in column H, it identifies from which table the data can be found.
Excelchat Expert
12/09/2018 - 08:16
You can simple clear filter in column G to show all your data.
User
12/09/2018 - 08:16
Yes it is very nice thanks. Can you explain the formula u used to me a bit
Excelchat Expert
12/09/2018 - 08:17
Sure so in column G:
Excelchat Expert
12/09/2018 - 08:17
cell G4 =IF(COUNTIF($B:$B,B4)>1,"C_"&TEXT(MATCH(B4,$B:$B,0),"000"),"")
Excelchat Expert
12/09/2018 - 08:18
So it is saying that if cell B4 (which has a value of ASCUTEC) recurs more than once (COUNTIF>1) in column B, then return the ID. Otherwise return blank or ""
Excelchat Expert
12/09/2018 - 08:19
The formula for the ID TEXT(MATCH(B4,$B:$B,0),"000")
Excelchat Expert
12/09/2018 - 08:19
MATCH returns the numerical position of the first instance of B4 (ASCUTEC) in your column ($B:$B)
Excelchat Expert
12/09/2018 - 08:20
So in this case ASCUTEC first occurs in row number 4. Then TEXT(4,"000") will transform this into 004.
Excelchat Expert
12/09/2018 - 08:20
So you can imaginge "C_"&"004" = C_004
User
12/09/2018 - 08:21
yes
Excelchat Expert
12/09/2018 - 08:21
Alright moving on the next column
Excelchat Expert
12/09/2018 - 08:21
cell H4 = =IF(A3<>"",A3,IF(B4<>"",H3,""))
Excelchat Expert
12/09/2018 - 08:21
This means that if cell A3 is not blank, then return the value of cell A3 (which is RF 4)
Excelchat Expert
12/09/2018 - 08:24
Now for more clarity on the other parts of the nested IF statement, let move to cell H5
Excelchat Expert
12/09/2018 - 08:24
H5 = IF(A4<>"",A4,IF(B5<>"",H4,""))
Excelchat Expert
12/09/2018 - 08:25
As you can see, A4 is blank. This means we move on to the next IF statement which is IF(B5M<>"",H4,"")
Excelchat Expert
12/09/2018 - 08:25
IF(B5<>"",H4,"")
Excelchat Expert
12/09/2018 - 08:27
This means that if the value in column B is a non-blank cell, then we pick return the value in cell above H5, which is H4.
User
12/09/2018 - 08:27
okay
User
12/09/2018 - 08:27
thanks
Excelchat Expert
12/09/2018 - 08:27
Sure! You're welcome :)
Excelchat Expert
12/09/2018 - 08:27
Do you have any more clarification?
User
12/09/2018 - 08:27
no, I think we are clear, thanks :)
User
12/09/2018 - 08:27
have a good day
Excelchat Expert
12/09/2018 - 08:28
You may end the session now by clicking END SESSION button on the upper right corner of the workstation
Excelchat Expert
12/09/2018 - 08:28
Appreciate if you could leave feedback and rating afterwards
User
12/09/2018 - 08:28
sure
Excelchat Expert
12/09/2018 - 08:28
Thanks and have a great day too!
Excelchat Expert
12/09/2018 - 08:28
Bye!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
