Logic problem for filtering a unique set of multiple values
Hello,
I am working on a spreadsheet to summarize data from chemical emissions reports and I am running into a very specific problem. The reports contain a ton of data but the columns pertinent here are the location (integer, 1 through 16ish), compound name (5 compounds) and sampling date (6-10 dates). So for each date, the sheet has entries for each of the 5 compounds for all the locations, totalling 5 x ~16 entries per date.
Example below, hopefully it formats okay.
| location | compound | date |
|---|---|---|
| 1 | Benzene | 04/20/25 |
| 1 | Ethylbenzene | 04/20/25 |
| 1 | m/p-xylenes | 04/20/25 |
| 1 | O-xylene | 04/20/25 |
| 1 | Toluene | 04/20/25 |
| 2 | Benzene | 04/20/25 |
| 2 | Ethylbenzene | 04/20/25 |
| 2 | m/p-xylenes | 04/20/25 |
| 2 | O-xylene | 04/20/25 |
| 2 | Toluene | 04/20/25 |
| 3 | Benzene | 04/20/25 |
| 3 | Ethylbenzene | 04/20/25 |
| 3 | m/p-xylenes | 04/20/25 |
| 3 | O-xylene | 04/20/25 |
| 3 | Toluene | 04/20/25 |
| … | … | … |
| 16 | 04/20/25 | |
| 1 | Benzene | 05/25/25 |
| 1 | Ethylbenzene | 05/25/25 |
| 1 | m/p-xylenes | 05/25/25 |
| 1 | O-xylene | 05/25/25 |
| 1 | Toluene | 05/25/25 |
I am currently trying to average the results for each compound at each site over all the dates. I have this part figured out fine, as long as I manually create the list of locations and compounds, but ideally this part would be done programatically. What I am looking for is a way to get a list of the locations and compounds for any undefined single date, so 1,1,1,1,1,2,2,2,2,2...etc all the way to 16 but then not repeating. If I use filter and some other functions I can either get a unique list (1,2,3...16) or I can get the full list (1,1,1,1,1,2...16,1,1,1,1,2...) but without explicitly telling the formula to pick one date I can't get it to do what I want.
It's right on the tip of my brain but I can't seem to figure this out.
What I am hoping to get:
| location | compound |
|---|---|
| 1 | Benzene |
| 1 | Ethylbenzene |
| 1 | m/p-xylenes |
| 1 | O-xylene |
| 1 | Toluene |
| 2 | Benzene |
| 2 | Ethylbenzene |
| 2 | m/p-xylenes |
| 2 | O-xylene |
| 2 | Toluene |
| 3 | Benzene |
| … | … |
| 16 | Benzene |
| 16 | Ethylbenzene |
| 16 | m/p-xylenes |
| 16 | O-xylene |
| 16 | Toluene |
[link] [comments]
Want to read more?
Check out the full article on the original site