We are trying to move some Excel sheets into Smartsheet!
In Excel, we have an address list in a sheet named “Main” and another sheet named “Street“.
We extract the unique street names from “Main” and list them in “Street” and we count the number of duplicates of the streets name.
My little sample has 7 addresses – the actual sheet has 4000.
(Street Number and Street Names are in separate columns.
In Excel sheet “Main” we have an address list:
Column A Column B
1 A St.
2 A St.
1 B Dr.
2 B Dr.
1 C Way
2 C Way
3 C Way
In another sheet in the same workbook, named “Streets” we include the following formular to find the Unique Street names. That is easy in Excel:
In “A1” cell we have:
=UNIQUE(Main!B1:B7)
When hits Return, it will list all street names.
But I cannot figure out how to duplicate the UNIQUE function in Smartsheet.
I am playing around with a combination of DISTINGT, MATCH and INDEX but I have not been able to get anything that will work.
Can anyone help?
The second half was easier.
That is where we count the number of addresses on each street.
In Excel, in the A1 cell type:
=COUNTIF(Main!A1:A11,A1)
That will count the residence on the first street.
Now we replicate this formular all the way to the last street name.
=COUNTIF(Main!A1:A11,A11)
In Smart Sheet this works OK:
=COUNTIF({Reference Range 1}, (Street1))
And then replicate this formular through the column.
Hope someone can point me in the right direction on the first part
