Hi

I have a spreadsheet which contains a record of item types that an
individual can sign for. However for each individual the item type is
repeated for each cost centre.
ie
Name Cost Centre Item
George Brown AAAA Invoices
George Brown AAAA Staff Return
George Brown AAAB Invoices
George Brown AAAB Staff Return
Ann Johns CCCD Invoices
Ann Johns CCCD Leave Forms
Ann Johns CCCD Expenses
Ann Johns CCCE Invoices
Ann Johns CCCE Leave Forms
Ann Johns CCCE Expenses

What I want is a macro that will look at the cost centre and delete any
lines from the persons record that has the same cost centre in it leaving one
entry per person per cost centre.

Name Cost Centre Item
George Brown AAAA Invoices
George Brown AAAB Invoices
Ann Johns CCCD Invoices
Ann Johns CCCE Invoices

This needs to continue until the macro reaches the last row containing any
data in the cost centre column.

I've tried the advanced filter for unique records but cannot do it this way.

In the spreadsheet the cost centres are actually oin column 4 and the
forename and family name are in column 1 and 2 respectively.

Thanks for any help.

I put your data in A1:C11
I selected A1:B11 (just the first two columns)
I did Data|filter|advanced filter
and
Filtered in place
and unique records only.

Then I selected those visible cells (A1:C9 in this sample)

and pasted to a new location:

Name Cost Centre Item
George Brown AAAA Invoices
George Brown AAAB Invoices
Ann Johns CCCD Invoices
Ann Johns CCCE Invoices

It looks pretty darn close to me.

Constantly Amazed wrote:

Hi

I have a spreadsheet which contains a record of item types that an
individual can sign for. However for each individual the item type is
repeated for each cost centre.
ie
Name Cost Centre Item
George Brown AAAA Invoices
George Brown AAAA Staff Return
George Brown AAAB Invoices
George Brown AAAB Staff Return
Ann Johns CCCD Invoices
Ann Johns CCCD Leave Forms
Ann Johns CCCD Expenses
Ann Johns CCCE Invoices
Ann Johns CCCE Leave Forms
Ann Johns CCCE Expenses

What I want is a macro that will look at the cost centre and delete any
lines from the persons record that has the same cost centre in it leaving one
entry per person per cost centre.

Name Cost Centre Item
George Brown AAAA Invoices
George Brown AAAB Invoices
Ann Johns CCCD Invoices
Ann Johns CCCE Invoices

This needs to continue until the macro reaches the last row containing any
data in the cost centre column.

I've tried the advanced filter for unique records but cannot do it this way.

In the spreadsheet the cost centres are actually oin column 4 and the
forename and family name are in column 1 and 2 respectively.

Thanks for any help.

--

Dave Peterson

Thanks Dave

After your reply I went back to the filter. The problem appeared to be the
header row. When I deleted the column headers and carried out the advanced
filter it produced the correct result except the first cost centre appeared
in rows one and two. Simply had to delete a row to give the result required.

G

"Dave Peterson" wrote:

I put your data in A1:C11
I selected A1:B11 (just the first two columns)
I did Data|filter|advanced filter
and
Filtered in place
and unique records only.

Then I selected those visible cells (A1:C9 in this sample)

and pasted to a new location:

Name Cost Centre Item
George Brown AAAA Invoices
George Brown AAAB Invoices
Ann Johns CCCD Invoices
Ann Johns CCCE Invoices

It looks pretty darn close to me.

Constantly Amazed wrote:

Hi

I have a spreadsheet which contains a record of item types that an
individual can sign for. However for each individual the item type is
repeated for each cost centre.
ie
Name Cost Centre Item
George Brown AAAA Invoices
George Brown AAAA Staff Return
George Brown AAAB Invoices
George Brown AAAB Staff Return
Ann Johns CCCD Invoices
Ann Johns CCCD Leave Forms
Ann Johns CCCD Expenses
Ann Johns CCCE Invoices
Ann Johns CCCE Leave Forms
Ann Johns CCCE Expenses

What I want is a macro that will look at the cost centre and delete any
lines from the persons record that has the same cost centre in it leaving one
entry per person per cost centre.

Name Cost Centre Item
George Brown AAAA Invoices
George Brown AAAB Invoices
Ann Johns CCCD Invoices
Ann Johns CCCE Invoices

This needs to continue until the macro reaches the last row containing any
data in the cost centre column.

I've tried the advanced filter for unique records but cannot do it this way.

In the spreadsheet the cost centres are actually oin column 4 and the
forename and family name are in column 1 and 2 respectively.

Thanks for any help.

--

Dave Peterson