Return a cross sheet value if occurs one time
I have a sheet called Employee Data with a State column with State abbreviations in it. I want to move that two digit state abbreviation to a new sheet called Labor Law Reference, column State if the state abbreviation occurs at least one time. On the Labor Law Sheet I only want it to occur in a row one time for each state that occurs in the Employee Data. In essence, i'm trying to collapse the duplicates or if it occurs one time into a row in new sheet.
Answers
-
HI @jjesmith , Are you looking for the Labor Law Reference sheet to simply list the distinct States from the Employee Data sheet? If so, you could do this with a report using the grouping feature. You could also do this with cross sheet references in your Labor Law Reference sheet using the DISTINCT() function and then using INDEX() to pull each distinct value (You would create an Index column with the numbers 1 through 50 for the index of the maximum number of states). The challenge here is that if you add additional rows to the Employee Data Sheet, the order of the States in the Labor Law Reference sheet may change. But perhaps that won't be a problem for your use case.
Let me know what you think of these solutions, and perhaps we can explore other options based on what you are trying to accomplish.
Be well
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!