Search code examples
google-sheetsgoogle-sheets-formula

How to combine data rows with different naming conventions in Google sheets?

I have a table that looks like this:

Road Accident
I-10 10
I-53 10
I-10 East 5
I-10/S 5

I'm looking for a way for the Accident counts are in for all versions of the I-10 and all other roads, so that the final table looks like this:

Road Accident
I-10 20
I-53 10

There are also different naming conventions for the same road, such as:

Road Accident
US STREET 10
US ST 10
5 AVENUE 5
5 AVE 5

And I'd like the table to look like

Road Accident
US STREET 20
5 AVENUE 10

Thanks in advance for your help!

I looked at this thread which seemed helpful, but it did not include a way to sum up the values of the other column into one.

Solution

  • Using ARRAYFORMULA() with SUMIF() and SUBSTITUTE()

    You can use this approach for better result and to get your expected result.

    in Column C2 use this formula:

    =IF(OR(A2 = "I-10 East", A2 = "I-10/S"), "I-10",
  IF(OR(A2 = "US ST", A2 = "US STREET"), "US STREET",
  IF(OR(A2 = "5 AVE", A2 = "5 AVENUE"), "5 AVENUE", A2)))

    C2

    use that approach to standardized road names. Suppose your data starts in column A and your new column is C

    For the Accident Counts you can use the Unique() and SUMIF() in Column E2 and F2

    Cell E2:

    =UNIQUE(ARRAYFORMULA(SUBSTITUTE(C2:C, "-", " ")))

    E2

    Cell F2:

    =ARRAYFORMULA(SUMIF(ARRAYFORMULA(SUBSTITUTE(C2:C, "-", " ")), E2:E, B2:B))

    F2

    For the disabling the dash "-" you can use Substitute()

    Cell D2:

    =ARRAYFORMULA(SUBSTITUTE(C2:C, "-", " "))

    D2

    Reference: