Populate Column With Unique Values From Another Sheet

Muhammad

Hi all!

I have a sheet, where tasks are entered and assigned to employees. Every task belongs to a certain company, which is specified row by row (every row is another task), this describes the first sheet we'll call "Requests". I want to create another sheet to summarize the data of every task for every company, this sheet we'll call "Hours by Company". The reason I say another sheet and not a report, is because I will have to do some custom formulas for data manipulation (to monitor hours spent of each company). As we have it setup, the company name is manually entered on every row for the "Requests" sheet, what I would like is to make a cross sheet reference to "Requests" and populate a column with all unique names found throughout the "Requests" in "Hours By Company". I'll Link below mockups of each sheet, as with just words this can be confusing:

Here is a mockup of the "Requests" sheet:

And here is a mockup of the "Hours by Company Sheet" I am trying to build:

The goal here is to grab all unique values from the "Company Name" Column in "Requests", and populate the "Company" Column in "Hours by Company". Is there a way to do this in Smartsheet? Thank you for any and all help!

Paul Newcome

Insert a text/number column (called "Number" in this example) and manually populate the numbers going down this column starting with the number 1 and going down until you have filled in as many numbers as you think you will need.

From there you would use this column formula in the Company column:

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Company Column}, {Source Sheet Company Column}, @cell <> "")), Number@row), "")

Paul Newcome

Insert a text/number column (called "Number" in this example) and manually populate the numbers going down this column starting with the number 1 and going down until you have filled in as many numbers as you think you will need.

From there you would use this column formula in the Company column:

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Company Column}, {Source Sheet Company Column}, @cell <> "")), Number@row), "")

Muhammad

https://community.smartsheet.com/discussion/comment/420892#Comment_420892

Thank you so much, this worked exactly as intended! Though there were more than 600 entries to fill 🤣

Paul Newcome

Happy to help. 👍️

ymckern

https://community.smartsheet.com/discussion/comment/420919#Comment_420919

Muhammad/Paul - was the number column that was added created in the "Requests" sheet or the "Hours by Company" sheet? I have a similar process that I'm trying to create and am just wanting to verify the Numbers column creation.

ymckern

@Paul Newcome @Muhammad

Paul Newcome

@ymckern It goes in the "Hours by Company" sheet.

ymckern

@Paul Newcome Thank you!!