0

In Google Sheets, I need to extract values of D in specific rows and insert them into column I where appropriate.

See link below. Here is an explanation of what I have so far in the spreadsheet:

  1. Raw data that I need to modify is in columns A:D.
  2. I have an ArrayFormula in E2 that pulls rows from A:D where C is not blank.
  3. Where C is blank, I need D to be inserted into I where E:F match A:C. <---This is what I can't figure out.

I'm not married to the formula in E2 if there is a more-efficient way to grab all of the information in one shot. Here's a sample of the data, and what I have so far.

I highlighted groups to show that there are sometimes duplicates or triplicates of the Zone. The value of D should go in I in line with the first iteration of the matching Route/Zone.

https://docs.google.com/spreadsheets/d/1xIum-fNKfdS4XWtpaHHJpluTUA3kUEDDMzStQsSxyi4/edit?usp=sharing

Here is the first 50 rows of what I have so far expressed as a CSV. Rows A:D are raw data and rows E:H are from this formula:

=ArrayFormula(SORT(ArrayFormula(IF(C2:C<>"",A2:D,IFERROR({A2:B,D1:D},""))),1,TRUE))

A B            C D E F             G H I
Route Zone Tote Packages Route Zone Tote In Tote NOT in Tote
102 J-18.1B Green 038 20 102 J-18.1B Green 038 20
102 J-18.1B 1 102 J-18.4A Orange 941 14
102 J-18.4A Orange 941 14 102 J-18.4A Green 362 14
102 J-18.4A Green 362 14 102 J-18.3A Black 906 13
102 J-18.4A 1 102 J-18.4C Navy 891 27
102 J-18.3A Black 906 13 102 J-18.1D Orange 288 16
102 J-18.3A 1 102 J-18.2D Navy 952 19
102 J-18.4C Navy 891 27 102 J-18.3D Black 909 14
102 J-18.4C 1 102 J-18.2B Green 577 31
102 J-18.1D Orange 288 16 102 J-18.3B Navy 528 17
102 J-18.1D 1 102 J-18.4B Yellow 405 12
102 J-18.2D Navy 952 19 102 J-18.4B Orange 920 12
102 J-18.2D 1 102 J-18.1C Green 272 19
102 J-18.3D Black 909 14 102 J-18.2C Orange 425 18
102 J-18.2B Green 577 31 102 J-18.3C Black 978 9
102 J-18.2B 3 103 H-18.2A Green 315 25
102 J-18.3B Navy 528 17 103 H-18.3A Orange 109 4
102 J-18.3B 3 103 H-18.4A Green 382 12
102 J-18.4B Yellow 405 12 103 H-18.1B Navy 662 10
102 J-18.4B Orange 920 12 103 H-18.4B Black 551 17
102 J-18.4B 4 103 H-18.3B Orange 334 28
102 J-18.1C Green 272 19 103 H-18.2B Green 456 25
102 J-18.1C 2 103 J-18.4D Black 741 14
102 J-18.2C Orange 425 18 103 J-18.1E Yellow 536 18
102 J-18.2C 1 103 J-18.2E Navy 771 17
102 J-18.3C Black 978 9 103 J-18.3E Yellow 838 23
102 J-18.3C 1 103 J-18.4E Orange 970 19
103 H-18.2A Green 315 25 103 J-18.3G Navy 298 15
103 H-18.2A 2 103 J-18.4G Black 688 19
103 H-18.3A Orange 109 4 103 H-18.1A Navy 170 13
103 H-18.3A 1 103 J-18.2G Orange 681 15
103 H-18.4A Green 382 12 103 J-18.1G Yellow 508 32
103 H-18.4A 1 104 H-18.2C Yellow 207 17
103 H-18.1B Navy 662 10 104 H-18.3C Navy 073 16
103 H-18.1B 1 104 H-18.1C Yellow 572 12
103 H-18.4B Black 551 17 104 H-18.4C Yellow 424 12
103 H-18.3B Orange 334 28 104 H-18.1D Green 087 17
103 H-18.3B 1 104 H-18.2D Orange 803 18
103 H-18.2B Green 456 25 104 H-18.3D Black 859 23
103 H-18.2B 1 104 H-18.4D Navy 525 30
103 J-18.4D Black 741 14 104 H-18.1E Yellow 227 22
103 J-18.4D 1 104 H-18.2E Black 021 22
103 J-18.1E Yellow 536 18 104 H-18.3E Navy 754 17
103 J-18.1E 4 104 H-18.4E Orange 925 15
103 J-18.2E Navy 771 17 104 H-18.1G Black 575 8
103 J-18.3E Yellow 838 23 104 H-18.2G Navy 087 12
103 J-18.3E 2 104 H-18.3G Orange 747 16
103 J-18.4E Orange 970 19 105 H-18.4G Black 928 25
103 J-18.4E 1 105 G-18.1A Green 504 17
8
  • While the likn to the spreadsheet might be helpful, questions on this site should be self contaiined. Please briefly describe the relevant parts of your spreadshet directly in the question body and include sample data. Show what you have tried including sample data, what you found from this site, from the official resources for end-users and why they didn't meet your needs. Please edit your question to provide the requested details. Once you have done that it can be reviewed, reopened, and answered. Commented Mar 22, 2023 at 22:55
  • If you need further help, please read first Guides to ask a [google-sheets] question. Commented Mar 22, 2023 at 22:55
  • I thought I did that. I'm not sure what else I can say... I described in detail what I was looking for. What information is missing?
    – Dustin
    Commented Mar 22, 2023 at 23:03
  • @Dustin add the sample data and the formula that you tried direclty into the question body. Include the data from the cell references used in your formula as well the the expected result. You don't have to all add all the rows that are included in the spreadsheet, add some representative rows. The best is that you create a small demo so the cell reference in the formula shown in the question matches the data shown in the question. Commented Mar 22, 2023 at 23:12
  • @Rubén I think I am closer to what you're looking for. I downloaded as CSV, opened in Notepad++, and copied/pasted the information. Problem is, I can't figure out how to get the carriage returns into the message, so I'm afraid it's all going to be one line.
    – Dustin
    Commented Mar 23, 2023 at 15:57

1 Answer 1

1

In cell I4, use query() to get the report, and vlookup() with a compound key to put the results in the correct rows, like this:

=arrayformula( 
  iferror( 
    vlookup( 
      E4:E & F4:F, 
      query( 
        { A4:D, A4:A & B4:B }, 
        "select Col5, sum(Col4) 
         where Col3 is null 
         group by Col5 
         label sum(Col4) '' ", 0 
      ), 
      2, false 
    ) 
  ) 
)

To remove results where there are priors, use countifs() in cell J4, like this:

=map( 
  E4:E, F4:F, I4:I, 
  lambda( 
    route, zone, notIn, 
    if( 
      1 = countifs(E4:route, route, F4:zone, zone), 
      notIn, 
      iferror(1/0) 
    ) 
  ) 
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.