Copy Data, Remove Duplicates & Sort Ascending

I am using Excel 2007 and what I'm hoping to do is to copy data from another sheet, remove duplicate values based on some criteria and sort data in ascending order based on the first three column.

I have a sheet named "Summary". I would like to copy data from columns B,D and AE starting from row three til last row with data. From column B, I would like to copy only the first character. The values will be pasted on sheet named "List" starting column A. After the values are copied, I would like to remove duplicates in Column B of sheet "List" based on values in Column A in the same worksheet. The following columns (D:I) will do the function "COUNTIFS."

I was able to edit some macro and applied it to do the computation. However, it takes some time before the calculation is completed.

Please Login or Register  to view this content.

If anyone could help me "refine my code" or provide me feedback, I would really appreciate it.

I have attached the sample workbook for your reference.

Thank you very much.

I've reordered things a little so you're not running those formula on the entire sheet.

Please Login or Register  to view this content.

Also, you are using whole column references for your named ranges, which may be contributing to lag, too. Instead, employ some dynamic range methods so the range is automatically expanded and contracted with the data. Here are your name ranges and my suggested Source: formulas:

Common_Name =Summary!$D$3:INDEX(Summary!$D:$D, MATCH("*", Summary!$D:$D, -1))
Plot =List!$A$3:INDEX(List!$A:$A, MATCH("*", List!$A:$A, -1))
Plot_No =Summary!$B$3:INDEX(Summary!$B:$B, MATCH("*", Summary!$B:$B, -1))
YR0_DBH =OFFSET(Common_Name,,1,,3)
YR1_DBH =OFFSET(Common_Name,,4,,3)
YR2_DBH =OFFSET(Common_Name,,7,,3)
YR3_DBH =OFFSET(Common_Name,,10,,3)
YR4_DBH =OFFSET(Common_Name,,13,,3)
YR5_DBH =OFFSET(Common_Name,,16,,3)
YR6_DBH =OFFSET(Common_Name,,19,,3)
YR7_DBH =OFFSET(Common_Name,,22,,3)