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.
Try QUERY() with REGEXEXTRACT().
=QUERY({INDEX(REGEXEXTRACT(A1:A4,"^[^ /]+")),B1:B4},"select Col1, sum(Col2) group by Col1 label sum(Col2) ''")
1
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)))
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, "-", " ")))
Cell F2:
=ARRAYFORMULA(SUMIF(ARRAYFORMULA(SUBSTITUTE(C2:C, "-", " ")), E2:E, B2:B))
For the disabling the dash "-" you can use Substitute()
Cell D2:
=ARRAYFORMULA(SUBSTITUTE(C2:C, "-", " "))
Reference:
- SUBSTITUTE ()
- UNIQUE function
- IF function
- ARRAYFORMULA ()
- SUMIF()