Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting frequency of strings in cells
Hi,
Given the table below. In column C, D etc I want to get the result of how frequent a certain port is mentioned. A B 1 2 Route 1 Alexandria, Port Said, Damietta, Port Said, Alexandria 3 Route 2 Port Said, Damietta, Gebze, Izmir, Port Said, 4 Route 3 Port Said, Alexandria, Port Said, Damietta, Port Said 5 Route 4 Port Said, Beirut, Tartous, Limassol, Port Said 6 Route 5 Port Said, Damietta, Istanbul, Port Said so if column C refers to Port Said and column D refers to Damietta, the results should be: C D 1 Port Said Damietta 2 2 1 3 2 1 4 3 1 5 2 0 6 2 1 Is this possible? Many thanks in advance and have a nice day! tom |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting frequency of strings in cells
Assuming you have port names in column 1 (i.e. Port Said in C1), then
you can use the following formula in C2 and copy down and across: (LEN($B2)-LEN(SUBSTITUTE($B2,C$1,"")))/LEN(C$1) HTH Kostis Vezerides tom ossieur wrote: Hi, Given the table below. In column C, D etc I want to get the result of how frequent a certain port is mentioned. A B 1 2 Route 1 Alexandria, Port Said, Damietta, Port Said, Alexandria 3 Route 2 Port Said, Damietta, Gebze, Izmir, Port Said, 4 Route 3 Port Said, Alexandria, Port Said, Damietta, Port Said 5 Route 4 Port Said, Beirut, Tartous, Limassol, Port Said 6 Route 5 Port Said, Damietta, Istanbul, Port Said so if column C refers to Port Said and column D refers to Damietta, the results should be: C D 1 Port Said Damietta 2 2 1 3 2 1 4 3 1 5 2 0 6 2 1 Is this possible? Many thanks in advance and have a nice day! tom |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting frequency of strings in cells
In cell C2 enter formula
=(LEN($B2)-LEN(SUBSTITUTE($B2,C$1,"")))/LEN(C$1) and drag it to the right and down as necessary. Regards, Stefi €˛tom ossieur€¯ ezt Ć*rta: Hi, Given the table below. In column C, D etc I want to get the result of how frequent a certain port is mentioned. A B 1 2 Route 1 Alexandria, Port Said, Damietta, Port Said, Alexandria 3 Route 2 Port Said, Damietta, Gebze, Izmir, Port Said, 4 Route 3 Port Said, Alexandria, Port Said, Damietta, Port Said 5 Route 4 Port Said, Beirut, Tartous, Limassol, Port Said 6 Route 5 Port Said, Damietta, Istanbul, Port Said so if column C refers to Port Said and column D refers to Damietta, the results should be: C D 1 Port Said Damietta 2 2 1 3 2 1 4 3 1 5 2 0 6 2 1 Is this possible? Many thanks in advance and have a nice day! tom |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting frequency of strings in cells
A none case sensitive version
=(LEN(UPPER(A1))-LEN(SUBSTITUTE(UPPER(A1),"PORT SAID","")))/LEN("PORT SAID") Sorry I meant to say for column D edit the text to Damietta and put it in upper case to make it none case sensitive. "Mike" wrote: In column C for Port Said use =(LEN(A2)-LEN(SUBSTITUTE(A2,"Port Said","")))/LEN("Port Said") And edit the text for other ports. Note this is case sensitive. "tom ossieur" wrote: Hi, Given the table below. In column C, D etc I want to get the result of how frequent a certain port is mentioned. A B 1 2 Route 1 Alexandria, Port Said, Damietta, Port Said, Alexandria 3 Route 2 Port Said, Damietta, Gebze, Izmir, Port Said, 4 Route 3 Port Said, Alexandria, Port Said, Damietta, Port Said 5 Route 4 Port Said, Beirut, Tartous, Limassol, Port Said 6 Route 5 Port Said, Damietta, Istanbul, Port Said so if column C refers to Port Said and column D refers to Damietta, the results should be: C D 1 Port Said Damietta 2 2 1 3 2 1 4 3 1 5 2 0 6 2 1 Is this possible? Many thanks in advance and have a nice day! tom |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting frequency of strings in cells
In column C for Port Said use
=(LEN(A2)-LEN(SUBSTITUTE(A2,"Port Said","")))/LEN("Port Said") And edit the text for other ports. Note this is case sensitive. "tom ossieur" wrote: Hi, Given the table below. In column C, D etc I want to get the result of how frequent a certain port is mentioned. A B 1 2 Route 1 Alexandria, Port Said, Damietta, Port Said, Alexandria 3 Route 2 Port Said, Damietta, Gebze, Izmir, Port Said, 4 Route 3 Port Said, Alexandria, Port Said, Damietta, Port Said 5 Route 4 Port Said, Beirut, Tartous, Limassol, Port Said 6 Route 5 Port Said, Damietta, Istanbul, Port Said so if column C refers to Port Said and column D refers to Damietta, the results should be: C D 1 Port Said Damietta 2 2 1 3 2 1 4 3 1 5 2 0 6 2 1 Is this possible? Many thanks in advance and have a nice day! tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting strings in excel with Pivot | Excel Discussion (Misc queries) | |||
Counting Text Strings With Conditions | Excel Discussion (Misc queries) | |||
Counting text strings | Excel Worksheet Functions | |||
Counting cells with a specified frequency(how many times it appear | Excel Discussion (Misc queries) | |||
counting unique strings | Excel Discussion (Misc queries) |