Update fields by largest string counts

[knozawa]

Hello,

I would like to update fields by the largest string counts.  The sample data is following:

Name	Specialty	Specialty String Count
Apple	Allopathic & Osteopathic Physicians|General Practice	52
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Hepatology	64
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology	75
Apple	Allopathic & Osteopathic Physicians|Surgery	43

In this case, string count "75" is the largest number.  Therefore, I would like to update Specialty field to "Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology".

Name	Specialty
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology

I was trying to use the Multi-Row formula.  I could only update one row, but not all rows.

I would appreciate if there is any suggestions.

Sincerely,

Kazumi

[Bob_Blackey]

Hi Kazumi,

I love the Multi_row formula tool but I didn't use it here.

I SORT the records so the highest count was the first record.

Then I used the SELECT RECORDS tool to get just the first record  (because the S.R. tool doesn't get much love)

Then I APPEND FIELDS to add the specialty name of the longest string to each record

Then I use SELECT to drop the original specialty and rename the longest specialty 

Cheers,

Bob

[knozawa]

Hi Bob,

Thank you for your reply.  This is good when I have only one name.  Sorry that I didn't tell you that I have more than one name.  For example, I have 3 names: Apple, Banana, and Orange.

Name	Specialty	Specialty String Count
Apple	Allopathic & Osteopathic Physicians|General Practice	52
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Hepatology	64
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology	75
Apple	Allopathic & Osteopathic Physicians|Surgery	43
Banana	Allopathic & Osteopathic Physicians|General Practice	52
Banana	Allopathic & Osteopathic Physicians|Internal Medicine|Hepatology	64
Orange	Allopathic & Osteopathic Physicians|General Practice	52
Orange	Allopathic & Osteopathic Physicians|Surgery	43

In this case, the desired output is following:

Name	Specialty
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
Banana	Allopathic & Osteopathic Physicians|Internal Medicine|Hepatology
Banana	Allopathic & Osteopathic Physicians|Internal Medicine|Hepatology
Orange	Allopathic & Osteopathic Physicians|General Practice
Orange	Allopathic & Osteopathic Physicians|General Practice

I think the select record tool does not work for this particular example.  Is there any way to filter out the largest Specialty String Count per person so that I can append later on like you did?

Sincerely,

Kazumi

[knozawa · Accepted solution]

Hi Bob,

I think I figured it out!

Thank you for your help.

Sincerely,

Kazumi

[Bob_Blackey]

Good job!

Bob