Return a cross sheet value if occurs one time

jjesmith

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.

Snip2.PNG

Snip1.PNG

Scott Orsey

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