Copy single row to new worksheet / delete blank rows

hi, stevejfice, can you post sample workbook?

Workbook should be attached.

Have just realised I'm missing a VLOOKUP from the Retentions worksheet, but this only pulled across the "Date Applied" from the Master Sales sheet, and there's probably a cleaner way to do it via macro anyway.

I hope people are understanding what I'm attempting to do

it would be helpful to see some fake data on sheet1 and what you expect to see on sheet2

Originally Posted by watersev

it would be helpful to see some fake data on sheet1 and what you expect to see on sheet2

The content isn't particularly important, but I've uploaded a few dummy cells on the relevant sheet now. What should be copied across is one line for each customer containing the Name and the Date they applied, and then the formulas automatically work out the dates we need to contact them (retention week 1, etc).

So, ultimately, even though I can have multiple lines per client on the first sheet, I only one one automatically created on the second.

please check attachment, run code "test", check sheet2

Originally Posted by watersev

please check attachment, run code "test", check sheet2

Wow, amazing! Thank you very much, you even got the dates right for the retention calls

Now if I could work out a way to automatically increment a sale on the statistics page at the same time that would be perfect, but I think that's above any kind of macro to work out that.

But again, thank you very much.

can you give some details for the third sheet, example data on all three sheets will be nice

Certainly can.

Each unique customer translates to one "Client", and each entry in the Lender column on Monthly Sales is a claim, case value is from the "amount" column on the same.

The issue I can foresee happening is Excel not being able to differentiate between the days of the week the client/claim is added (I have never seen it do anything that sophisticated), but you're welcome to give it a go if you think it's possible.

Broken down it means:
New Unique Client = 1 added to client column for that day
New claim for that client = 1 added to claim column for that day (each unique client can have anything up to a dozen or more claims)
Amount added the same days case value column.

I can guess you can tell that this would need to appear on different days, and weeks as the month goes by.

as it's a monthly sales sheet, it might happen that the same customer can appear a couple of times within one month but on a different date. What should happen then? (question for sheet2)

as it is now the code will take first instance of a cutomer name and corresponding date, all other instances of the same cutomer will be ignored. Is it correct.

Re: sheet3, visual example will be nice to get in addition to your explanation

It's very rare (as in it's never happened in the last six months) that we get a customer named the same thing twice, and I can't envisage it happening in the same month at all. The Customers that appear on this sheet then get handled by a separate part of the company who are lucky enough to have proper databases.

What about sample to support your explanation for sheet 3? How do you count weeks: starting from the 1 date of the month or the weekday it falls on? Is Statistics goes for one month or for the whole year?

Ok, I've transferred the data from the sheet you worked on for me, which translates to 2 clients, 2 claims and a total of £17,000 (and I've used today, the 4th Thursday of this month to make it easier).

If an agent was to get 3 clients and 6 claims tomorrow with a case value of £80,000 those values would appear in Friday, below the data present now.

The issue I can see occurring is how to make the data appear reflected against the relevant day. But then you seemed to fix my other problems as if they were nothing so here's hoping!