0
DATE NAME CX# DATA
11/7/2021 Alex CX55 1.34
11/7/2021 Linda CX43 22.9
11/7/2021 Loki CX109 3.43
11/8/2021 Alex CX 12 23
11/8/2021 Linda CX 113 2.49

What I am trying to do is, paste in a master sheet of data for a week, which is broken down by date, and by person. I need to formulate charts using data with a lookup where the data from Alex can be pulled and populated into another sheet for charting. Since the dates are going to be changing every week as new data is pasted/populated, I cannot do a normal vlookup and match based on unique static strings. Not only are dates changing, but even the names themselves day to day and week to week. Ideally I'd end up with something like this

DATE NAME DATA
11/7/2021 Alex 1.34
11/8/2021 Alex 23

using logic which is flexible enough to group when the data column is dynamic, as well as the name column. Maybe I am just not thinking if it in a way that makes sense with the functions available. Any guidance would help!

player0
  • 124,011
  • 12
  • 67
  • 124
jrodder
  • 23
  • 1
  • 3

1 Answers1

1

try:

=FILTER('Sheet1'!A:D, 'Sheet1'!B:B="Alex")

if your issue is that NAME column is not always under B column you can do:

=ARRAY_CONSTRAIN(QUERY({'Sheet1'!A:D, 
 FILTER('Sheet1'!A:D, 'Sheet1'!A1:D1="NAME")}, 
 "where Col5 = 'Alex'", 0), 9^9, 4)
player0
  • 124,011
  • 12
  • 67
  • 124
  • And that's the right track, but my issue is I cannot guarantee an "Alex" will always be a name in the report. I need to figure out a way to dynamically group by variable names separated by dates where the same name exists in multiple date rows. – jrodder Nov 23 '21 at 19:06
  • @jrodder can you share a copy of your sheet? – player0 Nov 23 '21 at 19:24
  • https://docs.google.com/spreadsheets/d/1vAnuEvEJ6nfMSeXD4YJEq5STe3z6dKPGjQ7tEamegs8/edit?usp=sharing – jrodder Nov 24 '21 at 16:21
  • I didn't mean to just paste a link, comment editing timed out! In any case that version is only slightly scrubbed. So you can see from that, I need to do things like pull all the data for a particular name (I guess in this case that's my PK) and then do other things, like create deltas and any other fun stuff and chart it out. This dataset will change weekly, including names and dates. – jrodder Nov 24 '21 at 16:30
  • @jrodder I checked your sheet... let me confirm if I understand it correctly... every month or so whole Sheet1 will change/update with new values (names, dates, numbers) and you would like to extract all values per given name/date from this day onwards (so if values will change next month you will not lose data of this month) if thats correct, then you will need to get script which will archive all current data into separate sheet in some pre-set intervals. see: https://stackoverflow.com/search?q=%5Bgoogle-apps-script%5D+%5Bgoogle-sheets%5D+archive&s=37b1f951-ef2e-4b27-b89c-d5a316042c2c – player0 Nov 24 '21 at 17:08
  • 1
    It would change every week, the report is rolling. English would be like "For all rows containing $name return and populate new sheet with new rows grouping by unique names." I think the root of my issue is how to deal with the dynamically changing number of rows, variable names, and variable dates. I like to take raw data, create a "transform" sheet, and then report off of the transform. Obviously a bit out of my element but trying to figure out how to tackle it. Maybe it has to be some kind of custom VB? – jrodder Nov 24 '21 at 20:01
  • @jrodder yes custom VB (script) is required for this task. I shall redirect you in scripting section of stackoverflow... – player0 Nov 24 '21 at 20:40