FINDING AND LISTING UNIQUE DATA IN A COLUMN

[cncrouterman]

I have a spreadsheet with data for cabinets for a job. The data includes
type, height, width, length, etc.

I have created additional columns, one for each unique part.
The cabinets are listed vertically, data defining each cabinet horizontally,
with my added columns tacked on.
Each unique part has its name generated by some of the defining data
So,
34h x 32w x 24deep base cabinet with unfinished ends would generate a name
for a left side base end of
END-L-34h24d-B-DS

So, with a hundred or so rows, and perhaps a dozen or more unique base end
left parts, I want to do two thing, 1 list each unique part name, and 2 count
(display) how many of those parts there are. These two goals apply to the
other parts as well, but I figure it will be simple to copy the solution to
the other columns

 

[Don Guillett]

You may? find
Data>subtotals
to be helpful

 

[Shane Devenshire]

Hi,

to generate a list of unique part name select the column with the part names
and choose Data, Filter, Advanced Filter, choose Copy to another location,
make sure the List range is your column of names, in the Copy to box enter
the cell address where you have room for the output, check Unique records
only and click OK.

Beside each result enter the following formula
=COUNTIF(D$1$1000,J2)

where J2 is the first output cell from the Advanced Filter and D11000 is
the range with the names. Copy the above formula down.

 

[cncrouterman]

Hi,

to generate a list of unique part name select the column with the part names
and choose Data, Filter, Advanced Filter, choose Copy to another location,
make sure the List range is your column of names, in the Copy to box enter
the cell address where you have room for the output, check Unique records
only and click OK.

Beside each result enter the following formula
=COUNTIF(D$1$1000,J2)

where J2 is the first output cell from the Advanced Filter and D11000 is
the range with the names. Copy the above formula down.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Thank you Shane,
I gave it a try, however, all of the source cells are effectively the same
formula, merely different because of the different relative references. The
result was that it yielded just one formula, not the value of the cells, and
so it did not provide the multiple values expected, further, because it
basically copied the formula the same as if done manually, the cell
references shifted as well, making the single result inaccurate anyway.
So far, the best I have been able to do is copy the VALUEs of the cells to
another sheet, sort, filter them out (unique) to create yet another list
which acts as a condition argument in a third list, which uses the countif
function on the copied values. I want to either have something within the
EXCEL workbook, or another program within my CAD/CAM software to read the
EXCEL data.
It works, but it is stupendously awkward. I am currently building a Macro
within my CAD/CAM program to approach the problem from another angle.
Unfortunately, the best solution is using a VB application within AlphaCAM
(the cad/cam program), and I have not yet acquired the necessary VB knowledge
to write it.