Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What formula to use?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What formula to use?
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 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What formula to use?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What formula to use?
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|