/docs/community?hl=en
/docs/community?hl=en
2/23/16
Original Poster
Leif Uhsadel

Combining join, sort, unique, transpose and filter

See this table:

I would like to list all ingredients similar to as done in cell A1. But I would like the list to be unique, and I would like it to be sorted by total amount given in row B. In case of duplicate entries sorting should take sum of values in row B into account.
Empty cells shall be ignored.

What I have is a list of all entries, that ignores empty fields. No sorting, taking care of double entries.
=arrayformula(concatenate(filter(A4:A31;A4:A31<>"")&", "))

I tried to combine the join-, sort-, unique-, transpose- and filter-command to generate the desired output but I cant get it to work. Note: any other way of solving this works for me, too.

Any help is appreciated!



Community content may not be verified or up-to-date. Learn more.
All Replies (1)
+Samantha
2/23/16
+Samantha
Hey there,

Thank you for taking the time to post your question to the Google Docs Help Forum.

You may be interested in using UNIQUE() in order to pull unique ingredients. Because some ingredients have different capitalization, I also used UPPER() and PROPER() to check for that and convert them back to a uniform capitalization.

I also used QUERY() instead of FILTER(), because I knew I could set multiple criteria and sort the results using one function. Here was the end result:

=arrayformula(concatenate(Proper(unique(upper(query(A4:B31;"select A where A!='' order by B Desc";0))))&", "))

Hope this helps! Please let me know if I can provide further assistance.


Best of luck,
Samantha
Was this reply helpful?
How can we improve it?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Expert - Google Employee — Googler guides and community managers
 
Expert - Community Specialist — Google partners who share their expertise
 
Expert - Gold — Trusted members who are knowledgeable and active contributors
 
Expert - Platinum — Seasoned members who contribute beyond providing help through mentoring, creating content, and more
 
Expert - Alumni — Past members who are no longer active, but were previously recognized for their helpfulness
 
Expert - Silver — New members who are developing their product knowledge
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.