Using Excel to analyse and batch

And it might be double-plus ungood. Much depends on how much you expect your readers to know or expect you to know: simple will be good for most but not impress an expert. And for a CV it all frankly strikes me as OTT. It says next to nothing about your competences.

You appear to be addressing the treatment for the employer. But what about the employee? Are you assuming there is a dispensation in place for the employee's subsistence? If so, say so. If not the amount ought to be returned by employer and employee and the employee claim a matching deduction; and you need to address NICs (see below).

But if the sweets were consumed as part of the meal they might be allowed or covered by the dispensation. Better example might be something manifestly personal - eg a light bulb to replace a blown one in his loo at home.

More generally, you have made life harder for yourself by picking use of a company credit card as the example. The result is subtly different rules from those for "benefits" the employer provides in kind. Eg

a. you are (potentially) into Class 1 NICs rather than Class 1A - ie both employer and employee may have to pay NICs on the 2. See eg Appendix 1 of CWG5.

b. the return on the P11D (NB not "PIID") (or P9D if you want to be comprehensive) ought to be under "vouchers and credit cards"

It's also quite a serious tax challenge and I'm retired so E&OE ;)

If you have no experience of payroll I am surprised you are you putting yourself forwards as someone who can deliver what an employer can buy elsewhere already proved. Indeed, I'd urge you to download and play with some of the free trials of commercial packages before you claim you can deliver the equivalent.

Wow, just what I need. Perhaps you (or anyone else for that matter) can advise me on how I might achieve the following. Preferably a method which works not only for Excel but for other spreadsheets (e.g. OpenOffice).

Having created (by hand) a sheet with four (or so) columns, where: column 1 might contain a transaction sequence number, column 2 an analysis category code, column 3 the amount, column 4 (and possibly a few other) various other details such as date and miscellaneous reference information and general remarks.

What I'd like it to do is to re-organise the sheet into two (or so) plus N columns, where N is the number of analysis category codes which are in use.

I'd like it to create a column-headings row (or I could supply this myself and get it to recognise them), and then for each row in the original, I want it to take the code in column 2 and use it as an index into the column headings, and to put the amount from column 3 into the column thus identified. Columns 1 and 4 of the riginal become columns 1 and 2 of the result.

Not clear? Example:

Input:

+---+----+-------+-----+ | 1 | A1 | 12.34 | ... | +---+----+-------+-----+ | 2 | A3 | 23.45 | ... | +---+----+-------+-----+ | 3 | B2 | 11.99 | ... | +---+----+-------+-----+ | 4 | A3 | 9.50 | ... | +---+----+-------+-----+

Output:

+---+-----+-------+-------+-------+ | | | A1 | A3 | B2 | +---+-----+-------+-------+-------+ | 1 | ... | 12.34 | | | +---+-----+-------+-------+-------+ | 2 | ... | | 23.45 | | +---+-----+-------+-------+-------+ | 3 | ... | | | 11.99 | +---+-----+-------+-------+-------+ | 4 | ... | 9.50 | | | +---+-----+-------+-------+-------+

Have a look at Pivot Tables in Excel. I'm not 100% sure, but they might just do what you want.

Well excuse me, but why do you have to have any sort of dispensation in place for Subsistence?

It is a right for you as a Sch D trader, or for your employer if you are Sch E, to recover subsistence as an allowable Travel expense - like a hotel bill if you are on a business trip.

I may not be an expert at tax but at least I know that.

You are reconfiguring arrays here - each of the above is the same array (or matrix) looked at from a different perspective or dimension.

Arrays are what Excel *is*, and "pivot tables" (as the other poster has said) are the ultimate array-handling mechanism.

Much more powerful however to use a macro to *process* your array, and in Excel that would be easy...but I get the feeling you are not in Excel.

Yes in order to avoid the need to report them.

I do not understand your use of the term "recover". Do you mean "deduct" (when computing trading profits)?

I shall of course defer to your greater knowledge. Do please forgive me for thinking that you might not have read ITEPA, the Employment Income Manual, Booklet 480, CWG5, etc etc. I shall try not burden you with further comments.

Well you still miss the point. Why pay for a piece of software which covers billions of complicated possibilities of what might or might not happen, and take the time to learn it, when you can simply ascertain the facts in your own particular case, and write a schedule that deals with it.

I do appreciate your comments and I'm going to go through them over the weekend, when I have more time.

Thanks.

Two other methods;

Copy then PasteSpecial/Transpose might gove you what you want.

But the easiest and best method is to take a button from the Control Toolbox, double click it to go to its code, then inside the Private Sub...End Sub text write:

Set R = Range("A12").CurrentRegion.Offset(1, 0) Set R = R.Resize(R.Rows.Count-1, R.Columns.Count)

...which defines your existing database on or around Cell A12...

Set ptr = Range("H12")

For i = 0 to R.Rows.Count - 1 For j = 0 to R.Columns.Count - 1 S = R.Cells(1).Offset(1, j) '### 'here you can process S '### ptr.Offset(j, i).Value=S Next Next

...and you have written your own Pivot Table!

Oh dear, oh dear. Comes in to the finance group for advice and then tries to get up the nose of people trying to help him.

I bet you have succeeded

I think this is what you want:

Put you starting data in Rows 1 to 4, columns A to C as below

1 A1 12.34 2 A3 23.45 3 B2 11.99 4 A3 9.5

Put you results table in columns F,G,H,I and rows 2,3,4,5

ie F2 contains the number 1 and G1 contains the text A1

A1 B2 A3

1 2 3 4

In cell G2 put:

=IF(INDIRECT("R"&$F2&"C2",FALSE)=G$1,INDIRECT("R"&$F2&"C3",FALSE),"")

and copy that in to all of the cells of your answer table.

.

You should get:

A1 B2 A3

1 12.34 2 23.45 3 11.99 4 9.5

which I think is what you want.

I can explain if needed

Yes, that works, thanks. At least it does in openoffice, but sadly not in gnumeric.

Someone suggested pivot tables, but I don't use excel. However, openoffice's equivalent, "data pilot", also works, and wasn't too difficult to figure out how to drive.

Whilst both the above solutions "work", I don't really regard either of them as suitable for practical use, because having to manually configure the "pilot" (or, in the case of your solution, having to manually pre-populate the results table) each time I want it to process a new raw table for me is completely unsatisfactory. I'm more of a command-line kinda guy, and I want to write the solution once and then apply it often; writing it each time is just not on.

I must confess that the problem as I presented it was only a simplified version of what I really want to do, which is create a somewhat denser table than one which has only one of the category columns populated per row.

As you can see, the original input table has sequence or transaction numbers in the first column, category code names in the second, and amounts in the third. There are additional description columns, but they are not relevant here.

As first presented, each row had a unique sequence number, but I also want to be able to give several contiguous rows the same sequence number, and for these to appear as a single row in the output table. Where more than one such input rows also involve the same category name, I want the output cell for that category to be the relevant sum.

Like this:

For input fragment

2 A3 12.34 2 B2 23.45 2 A1 11.22 2 A3 22.33

I want the output to be

N A1 A3 B2

2 11.22 34.67 23.45

I dare say you'll be able to devise a way to make excel or openorifice do that, but frankly I think it would be easier to process the file (once converted to a simple format such as CSV) by a custom program entirely independently of a spreadsheet.

This is the most trivial possible of all things to do, and no one will have written a "custom program" to do such a simple reconfiguration of an array.

Just turn the bloody array around!

I don't understand what you mean by "gnumeric" but whatever it is it must be daft. Maybe move to a more recognisable platform?

No it isn't.

No, the illustration was only a fragment of the array, and this problem is harder than the one I initially described. Essentially the input as I first described it (where each row has a unique sequence number in column 1) is a 3-column-wide way of populating an ouptut table which is sparse in the sense that it only has one proper data value per row. In fact the output table has as many rows as the input has (plus one for the heading row), and it has as many columns as the input has different values in column 2 (plus one if retaining the sequence number column). But in the second problem the output table is a bit more densely populated, so it typically has fewer rows than the input has (it has as many rows as the input column 1 has different values (plus one)).

Gnumeric is one of the spreadsheets which comes with linux.

I must have missed your Excel solution to the problem.

Would you mind explaining it - I am sure that others would like to see the answer from an expert.