Looking portential formatting macro to include specific sales copy and paste.

**lachy725** · 05-29-2012, 09:08 PM

Hello Excelforum,

I have been using excel quite a lot in the last 3 years but have mostly just used the general formulae in the certain cells required. I am attempting to increase my excel skills by potentially learning some macros and programming skills. I understand there are other threads related to introductory VBA and macro works which I have had a look at.

Unfortunately I have not been able to find a specific solution to the problem I am facing. I have uploaded a sample spreadsheet of what I am requiring and am wondering if it would be possible to use a macro to make my work a lot easier.

Their are certain reports which I can import into excel for work which are very useful, but I would also like to manipulate these reports to analyse the data in a different way.

Basically what I require to do is to insert a new column on the left of the rest of the works and fill this column with the associated customer name so that each row of data has an attachment to the customer and not just under the customer's heading (I hope this makes sense). Once the customer's name has been added in the new column for each row of purchases, the row which contains the customer's name will then be deleted.

Let me know if this can be done efficiently with a macro or if some other programming may need to be rquired. Once again the inital report style is in the attached spreadsheet and a sample of what I am after is on another page of the same spreadsheet.

Thank you in advance,

Lachy725

**TMS** · 05-29-2012, 09:29 PM

Once you have added your column, put the following formula in cell A3:

=IF(LEFT(B3,8)="Customer",B3,A2)

And copy down.

Then copy all of column A and Paste Special | Values.

You can then filter on column B to select the Customer headers and delete the rows.

For a macro, record these steps as you do them manually.

Regards, TMS

**lachy725** · 05-29-2012, 11:15 PM

Thanks TMS,

Your methodology makes sense and am able to follow it well.

I assume that the function basically asks that if the first 8 letters in the cell are "customer" use that cell if not use the cell above. Unfortunately the report that I have does not have any such similiarities. Eash customer is not named as "customer - A" or "customer B" but more general names like "Smith's Services" or "Dave Pty Ltd".

A way of differentiating from the sales types and customer rows is obviously required to use your IF formula. I don't think that there are any text related differences that you could determine the customer rows from. The only identifying factor I can think of is the fact that the cells are in navy blue and are in size 11 not size 9.

Can you differentiate a cell by size or colour?

Thanks for the hasty response.

EDIT: In summary - Is there a way for an IF Statement's logical test to be based on text height or text colour?

**TMS** · 05-30-2012, 01:55 AM

It would, perhaps, be helpful if your sample data was representative of the real world.

If the cell next to the customer header is always blank, you could test for that.

Regards, TMS