Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FINDING AND LISTING UNIQUE DATA IN A COLUMN
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FINDING AND LISTING UNIQUE DATA IN A COLUMN
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:D$1000,J2) where J2 is the first output cell from the Advanced Filter and D1:D1000 is the range with the names. Copy the above formula down. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "cncrouterman" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FINDING AND LISTING UNIQUE DATA IN A COLUMN
"Shane Devenshire" wrote: 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:D$1000,J2) where J2 is the first output cell from the Advanced Filter and D1:D1000 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Names listing Data from one Column! | Excel Discussion (Misc queries) | |||
Finding unique data between two diffrent spreadsheets | Excel Discussion (Misc queries) | |||
Finding unique data between 2 columns | Excel Worksheet Functions | |||
Finding unique numbers in a column | Excel Discussion (Misc queries) | |||
Finding Unique Values in Column | Excel Discussion (Misc queries) |