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
Solved! Go to Solution.
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
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
Good job!
Bob