What formula to use?

[katrina]

Hi There

I am trying to combine data from one s'sheet to another depending on
conditions.

I am trying to combine date from different spreadsheets into one,
named template.xls. The first sheet I am trying to draw data from is
called lodgement.xls. Common values between the two sheets are
customer number and customer name. In template.xls each customer has
one line entry however in lodgement.xls if a customer has more than
one lodgement point they have a seperate line for each new lodgement
point.

Each lodgement point is tied to a lodgement zone, there are 7 possible
lodgment zones.

Template,xls is set up as follows

A1 = Cust No.
b1 = Cust Name
c1 = LZ 1
d1 = Lz2
e1 = Lz3
f1 = Lz4
g1 =LP 1
g2 = LP 2
etc

i want to be able to determine if the customer from template.xls
appears in lodgement point.xls
if the customer has one than one lodgment zone i would like them to
appear in cells c2, d2, e2 etc.
Does anyone know what formula I would use to do this?
Thanks
katrina

 

[Pete_UK]

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

 

[katrina]

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

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

 

[Pete_UK]

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