Thanks for the help. The macro works pretty well but i need some more help.
First let me recap the task...
here is the data...
"Count","ComponentName","RefDes","Value","Description"
"1","24PIN LCD CONNECTOR","J2","CON_LCD",""
"1","128MEG SDRAM 32X4 ","U2","",""
"4","SMT CAP 0402 100NF","C12","100nF",""
"","SMT CAP 0402 100NF","C18","100nF",""
"","SMT CAP 0402 100NF","C30","100nF",""
"","SMT CAP 0402 100NF","C33","100nF",""
The first row is the header. the following is the data. the range is
dynamic, meaning it could be 1 row or 30 rows long.
I need to format the data as below...
"Count","ComponentName","RefDes","Value","Description"
"1","24PIN LCD CONNECTOR","J2","CON_LCD",""
"1","128MEG SDRAM 32X4 ","U2","",""
"4","SMT CAP 0402 100NF","C12, C18, C30, C33","100nF",""
basically here is what i need to do...compare the data from one row, and
ComponentName column. If the data is the same, update RefDes with the RefDes
from the duplicate row.
IE: "4","SMT CAP 0402 100NF","C12","100nF",""
"","SMT CAP 0402 100NF","C18","100nF",""
should be...
"4","SMT CAP 0402 100NF","C12, C18","100nF",""
At this point the duplicate row should be deleted....side note: as you can
see the data already has some totalled "Count" numbers in the data. If the
data is out of order it may not have it totaled up correctly. what needs to
be done here is clear the contents of the "Count" column and place a 1 in
the cell. This way we can increment the count column every time we add to
the "RefDes" column.
Some of this works from the macro provided below however not all of it.
- The contents of the "count" column is not correct.
- The macro deletes the duplicate data but then does not resort the data so
it is full of spaces! I have to rerun the macro to corrct this.
- Sorting the data (reruning the macro) sorts the data via the
"ComponentName" column without headers so the header row is sorted...
ie:
"1","24PIN LCD CONNECTOR","J2","CON_LCD",""
"1","128MEG SDRAM 32X4 ","U2","",""
"Count","ComponentName","RefDes","Value","Description"
"4","SMT CAP 0402 100NF","C12","100nF",""
"","SMT CAP 0402 100NF","C18","100nF",""
"","SMT CAP 0402 100NF","C30","100nF",""
"","SMT CAP 0402 100NF","C33","100nF",""
Now once the data is compared and sorted I have one last step. I would like
to format the "RefDes" column.
ie: C12, C18, C30, C33 would be for the above data.
Other data might be: C12, C13, C14, C18, C30, C33
This data should be formated C12-C14, C18, C30, C33
The last part might not be possible. The first part is close with the macro
but needs some tweaking.
Any help on cleaning this up would be appreciated. Thanks.
