Well tell me what columns you have in your lodgement sheet, and how
these relate to the template sheet columns. In your example you seem
to have a column for LZ, and another one for LP, and you imply that
there would be no more than 4 duplicates by having LZ1, LZ2, LZ3, LZ4,
LP1, LP2. Would it not be better to arrange these as LZ1, LP1, LZ2,
LP2, LZ3, LP3 etc. in case you have more than 4 duplicates?

Pete

On Dec 7, 12:34 am, katrina wrote:
On Dec 7, 10:17 am, Pete_UK wrote:





In your lodgement.xls sheet, assume that the customer number is in
column A, starting at A2. Then in the first empty column (assume it is
column E), you can put this formula in E2:

=A2&"_"&COUNTIF(A$2:A2,A2)

and copy this down to cover your data. This will give you a unique
reference number for each record, linked to the customer number
sequentially, eg 01234_1, 01234_2, 01234_3 etc for customer number
01234.

Then in your template.xls sheet you will be able to use a MATCH
function together with INDEX to retrieve the data from the first
record for each customer number, and as this is copied across it can
retrieve the second, third, fourth record for each customer, dependent
on the column that the formula is in.

Hope this helps.

Pete

On Dec 6, 11:51 pm, katrina wrote:

Hi Pete

The first part worked but am totally lost when you mention match and
index ... how does this work?
thanks
katrina- Hide quoted text -

- Show quoted text -