Hi
is there any good excel forum where i can post my excel to get some answers please?
thanks
Excel Forum
sheya on 09/04/2016 - 20:28
Comments
Try here as well, there are some smarty pants among us who might be able to help.
but i cant upload excel here?
anyway i will ask anyway..
i have the below 2 tables.Settlement table
CONTRACT_INT CLOS_PER CALCFROM CALCTO DAYS_NUMB RATE AMOUNT CALC_AMOUNT USE_FROM USE_TO EVAL_FROM EVAL_TO AMOUNT_CC LST track?
50024BEC34530A60E10080000A1F20F1 2016006 20,160,229,120,000 20,160,329,120,000 29 2.4217 1,907.68 1,907.68 29.02.2016 29.03.2016 29.02.2016 29.03.2016 1,907.68
50024BEC34530A60E10080000A1F20F1 2016004 20,160,128,120,000 20,160,229,120,000 32 2.4217 2,105.02 2,105.02 28.01.2016 29.02.2016 28.01.2016 29.02.2016 2,105.02
50024BEC34530A60E10080000A1F20F1 2016002 20,151,229,120,000 20,160,128,120,000 30 2.4217 1,973.46 1,973.46 29.12.2015 28.01.2016 29.12.2015 28.01.2016 1,973.46
50024BEC34530A60E10080000A1F20F1 2015024 20,151,130,120,000 20,151,229,120,000 29 2.24 1,764.54 1,764.54 30.11.2015 29.12.2015 30.11.2015 29.12.2015 1,764.54
50024BEC34530A60E10080000A1F20F1 2015022 20,151,028,120,000 20,151,130,120,000 33 2.24 2,007.93 2,007.93 28.10.2015 30.11.2015 28.10.2015 30.11.2015 2,007.93
50024BEC34530A60E10080000A1F20F1 2015020 20,150,928,120,000 20,151,028,120,000 30 2.24 1,825.39 1,825.39 28.09.2015 28.10.2015 28.09.2015 28.10.2015 1,825.39
50024BEC34530A60E10080000A1F20F1 2015018 20,150,828,120,000 20,150,928,120,000 31 2.185 1,839.92 1,839.92 28.08.2015 28.09.2015 28.08.2015 28.09.2015 1,839.92
50024BEC34530A60E10080000A1F20F1 2015016 20,150,728,120,000 20,150,828,120,000 31 2.185 1,839.92 1,839.92 28.07.2015 28.08.2015 28.07.2015 28.08.2015 1,839.92
50024BEC34530A60E10080000A1F20F1 2015014 20,150,629,120,000 20,150,728,120,000 29 2.185 1,721.22 1,721.22 29.06.2015 28.07.2015 29.06.2015 28.07.2015 1,721.22
50024BEC34530A60E10080000A1F20F1 2015012 20,150,528,120,000 20,150,629,120,000 32 2.28 1,981.85 1,981.85 28.05.2015 29.06.2015 28.05.2015 29.06.2015 1,981.85
50024BEC34530A60E10080000A1F20F1 2015010 20,150,428,120,000 20,150,528,120,000 30 2.28 1,857.99 1,857.99 28.04.2015 28.05.2015 28.04.2015 28.05.2015 1,857.99
50024BEC34530A60E10080000A1F20F1 2015008 20,150,227,120,000 20,150,330,120,000 31 2.83 2,383.06 2,383.06 27.02.2015 28.04.2015 27.02.2015 28.04.2015 2,383.06
50024BEC34530A60E10080000A1F20F1 2015008 20,150,330,120,000 20,150,428,120,000 29 2.28 1,796.05 1,796.05 27.02.2015 28.04.2015 27.02.2015 28.04.2015 1,796.05
50024BEC34530A60E10080000A1F20F1 2015004 20,150,128,120,000 20,150,227,120,000 30 2.83 2,306.19 2,306.19 28.01.2015 27.02.2015 28.01.2015 27.02.2015 2,306.19
50024BEC34530A60E10080000A1F20F1 2015002 20,141,229,120,000 20,150,128,120,000 30 2.83 2,306.19 2,306.19 29.12.2014 28.01.2015 29.12.2014 28.01.2015 2,306.19
50024BEC34530A60E10080000A1F20F1 2014013 20,140,929,120,000 20,141,010,120,000 11 2.79 833.64971 833.64971 29.09.2014 10.10.2014 29.09.2014 10.10.2014 833.64971
50024BEC34530A60E10080000A1F20F1 2014013 20,141,010,120,000 20,141,229,120,000 80 2.79 6,062.91 6,062.91 10.10.2014 29.12.2014 10.10.2014 29.12.2014 6,062.91
50024BEC34530A60E10080000A1F20F1 2014009 20,140,630,120,000 20,140,929,120,000 91 2.755 6,810.04 6,810.04 30.06.2014 29.09.2014 30.06.2014 29.09.2014 6,810.04
50024BEC34530A60E10080000A1F20F1 2014005 20,140,328,120,000 20,140,630,120,000 94 2.74 6,996.25 6,996.25 28.03.2014 30.06.2014 28.03.2014 30.06.2014 6,996.25AND
LST table
CONTRACT_INT TRACK_ID LST_VALID_FROM LST_VALID_TO ZLST_REPEAT_PER ZLST_REPEAT_UNIT
50024BEC34530A60E10080000A1F20F1 L01 28/03/14 28/12/14 3
50024BEC34530A60E10080000A1F20F1 L01 29/12/14 28/03/16 1
50024BEC34530A60E10080000A1F20F1 L01 29/03/16 28/03/17 3
50024BEC34530A60E10080000A1F20F1 L01 29/03/17 31/12/99 3Q1 for each contract int in settlement table,
if the clos per is the same add days_numb of those 2 rowsI have a large number of rows and subtotal puts an extra row at the bottom which is no good.
I just want a column to have this info.Q2
for each contract int in settlement tab find the LST info from the LST (2) tabthe contract int must match
the settlement tab's calc from and calc to must be within the LST tab's valid from and valid to.
I have a very round about way of doing this - i.e. using yeses and nos..
being a newbie whats the quickest way of doing this please?Many thanks
By the look of the set up it is your homework, I don't think here is a good place for you to ask someone for answer.
Also the way you copy and paste is very hard to answer your questions.
if you have a specific formula/Macro question I can give you a hand but I wont help you with your homework.This mean for you to learn.
I realize the table didnt paste well.
this is not homework - just trying to see if i can fix this excel for someone.Settlement table
CONTRACT_INT CLOS_PER CALCFROM CALCTO DAYS_NUMB new col needed LSTinfo
11 2016006 20,160,229,120,000 20,160,329,120,000 29
11 2016004 20,160,128,120,000 20,160,229,120,000 32
11 2016002 20,151,229,120,000 20,160,128,120,000 30
11 2015024 20,151,130,120,000 20,151,229,120,000 29
11 2015022 20,151,028,120,000 20,151,130,120,000 33
11 2015020 20,150,928,120,000 20,151,028,120,000 30
11 2015018 20,150,828,120,000 20,150,928,120,000 31
11 2015016 20,150,728,120,000 20,150,828,120,000 31
11 2015014 20,150,629,120,000 20,150,728,120,000 29
11 2015012 20,150,528,120,000 20,150,629,120,000 32
11 2015010 20,150,428,120,000 20,150,528,120,000 30
11 2015008 20,150,227,120,000 20,150,330,120,000 31
11 2015008 20,150,330,120,000 20,150,428,120,000 29
11 2015004 20,150,128,120,000 20,150,227,120,000 30
11 2015002 20,141,229,120,000 20,150,128,120,000 30
11 2014013 20,140,929,120,000 20,141,010,120,000 11
11 2014013 20,141,010,120,000 20,141,229,120,000 80
11 2014009 20,140,630,120,000 20,140,929,120,000 91
11 2014005 20,140,328,120,000 20,140,630,120,000 94LST table
CONTRACT_INT TRACK_ID LST_VALID_FROM LST_VALID_TO ZLST_REPEAT_PER ZLST_REPEAT_UNIT
11 L01 28/03/14 28/12/14 3
11 L01 29/12/14 28/03/16 1
11 L01 29/03/16 28/03/17 3
11 L01 29/03/17 31/12/99 3
Is this anyway related to some GAS business in NSW?
For future reference, your data extracts aren't very clean - recipients would need to perform some data scrubbing before they can use it.
In regards to the format of your data extract, delimiting by space is ok but it's not common to do so (as any text/data with spaces, such as names or string text, will throw off data importing). If you must supply data in a non-Excel format, it's more common to export it in CSV (comma separated values) format. You can do this under 'Save As' and click on the 'Save as type' drop down list under the File name field. Note this requires your data, especially numbers, to not have commas in it. which you For numbers with commas you can remove it through cell format before exporting. For text/string fields with commas you'll have to manually edit the data or just go with a delimited space file export.
In regards to the actual data values, your Settlement table data has commas in what I'm guessing should be dates so it's not clear to a recipient what these values are. My guess would be times along with dates in the same column, in which case it's good practice to separate this into two columns to avoid ambiguity.
I guess ms access is more suited to this type of query..
just wanted your thoughts - if this can be done via excel?Can you provide a link to the actual excel workbook via Google Drive, dropbox, etc.?
Ctrl+Alt+Del
Q1 if the clos per is the same add days_numb of those 2 rows
Not sure where you want to output the result of adding two days_numb fields together - in a new column appended at the end, overwriting an existing column or in a new table somewhere else?Regardless, it looks like you want the SUMIF function, with the criteria based on the CLOS_PER data.
Q2 for each contract int in settlement tab find the LST info from the LST (2) tab
Try a VLOOKUP function.the contract int must match
VLOOKUP will do this if you set the last condition of the formula to FALSE, it'll ensure the lookup will only work on a perfect match and will return N/A otherwise.the settlement tab's calc from and calc to must be within the LST tab's valid from and valid to
See my previous post, it's not clear what data is in your CALC_FROM and CALC_DATA fields - dates and times? I'll assume you're trying to match data on the condition that a range of dates in the Settlement table lies overlaps at any point with the date range of the LST table. For this you'll probably need to perform a nested IF statement (multiple IF statements in the same formula). Should the ranges match up in your IF statement then let it perform the VLOOKUP.thanks Bengal Tiger Is this better?
i would normally just extract these in ms-access and then write some select statements..
but my data is rather huge so i am wondering if i could directly do this in excel itself?LST table
CONTRACT_INT,TRACK_ID,LST_VALID_FROM,LST_VALID_TO,ZLST_REPEAT_PER,ZLST_REPEAT_UNIT
11,L01,28/03/14,28/12/14,3,
11,L01,29/12/14,28/03/16,1,
11,L01,29/03/16,28/03/17,3,
11,L01,29/03/17,31/12/99,3,settlement table
CONTRACT_INT,CLOS_PER,CALCFROM,CALCTO,DAYS_NUMB,RATE,AMOUNT,CALC_AMOUNT,USE_FROM,USE_TO,EVAL_FROM,EVAL_TO,AMOUNT_CC,LST track?,,,,?,Calc from,Calc to,,
11,2016006,"20,160,229,120,000","20,160,329,120,000",29,2.4217,"1,907.68","1,907.68",29.02.2016,29.03.2016,29.02.2016,29.03.2016,"1,907.68",,2016,03,29,29/03/2016,29/02/2016,29/03/2016,28/03/2014,FALSE
11,2016004,"20,160,128,120,000","20,160,229,120,000",32,2.4217,"2,105.02","2,105.02",28.01.2016,29.02.2016,28.01.2016,29.02.2016,"2,105.02",,2016,01,28,28/01/2016,28/01/2016,28/01/2016,29/12/2014,FALSE
11,2016002,"20,151,229,120,000","20,160,128,120,000",30,2.4217,"1,973.46","1,973.46",29.12.2015,28.01.2016,29.12.2015,28.01.2016,"1,973.46",,2015,12,29,29/12/2015,29/12/2015,29/12/2015,29/03/2016,FALSE
11,2015024,"20,151,130,120,000","20,151,229,120,000",29,2.24,"1,764.54","1,764.54",30.11.2015,29.12.2015,30.11.2015,29.12.2015,"1,764.54",,2015,11,30,30/11/2015,30/11/2015,30/11/2015,29/03/2017,FALSE
11,2015022,"20,151,028,120,000","20,151,130,120,000",33,2.24,"2,007.93","2,007.93",28.10.2015,30.11.2015,28.10.2015,30.11.2015,"2,007.93",,2015,10,28,28/10/2015,28/10/2015,28/10/2015,0/01/1900,FALSE
11,2015020,"20,150,928,120,000","20,151,028,120,000",30,2.24,"1,825.39","1,825.39",28.09.2015,28.10.2015,28.09.2015,28.10.2015,"1,825.39",,2015,09,28,28/09/2015,28/09/2015,28/09/2015,0/01/1900,FALSE
11,2015018,"20,150,828,120,000","20,150,928,120,000",31,2.185,"1,839.92","1,839.92",28.08.2015,28.09.2015,28.08.2015,28.09.2015,"1,839.92",,2015,08,28,28/08/2015,28/08/2015,28/08/2015,0/01/1900,FALSE
11,2015016,"20,150,728,120,000","20,150,828,120,000",31,2.185,"1,839.92","1,839.92",28.07.2015,28.08.2015,28.07.2015,28.08.2015,"1,839.92",,2015,07,28,28/07/2015,28/07/2015,28/07/2015,0/01/1900,FALSE
11,2015014,"20,150,629,120,000","20,150,728,120,000",29,2.185,"1,721.22","1,721.22",29.06.2015,28.07.2015,29.06.2015,28.07.2015,"1,721.22",,2015,06,29,29/06/2015,29/06/2015,29/06/2015,0/01/1900,FALSE
11,2015012,"20,150,528,120,000","20,150,629,120,000",32,2.28,"1,981.85","1,981.85",28.05.2015,29.06.2015,28.05.2015,29.06.2015,"1,981.85",,2015,05,28,28/05/2015,28/05/2015,28/05/2015,0/01/1900,FALSE
11,2015010,"20,150,428,120,000","20,150,528,120,000",30,2.28,"1,857.99","1,857.99",28.04.2015,28.05.2015,28.04.2015,28.05.2015,"1,857.99",,2015,04,28,28/04/2015,28/04/2015,28/04/2015,0/01/1900,FALSE
11,2015008,"20,150,227,120,000","20,150,330,120,000",31,2.83,"2,383.06","2,383.06",27.02.2015,28.04.2015,27.02.2015,28.04.2015,"2,383.06",,2015,02,27,27/02/2015,27/02/2015,27/02/2015,0/01/1900,FALSE
11,2015008,"20,150,330,120,000","20,150,428,120,000",29,2.28,"1,796.05","1,796.05",27.02.2015,28.04.2015,27.02.2015,28.04.2015,"1,796.05",,2015,03,30,30/03/2015,30/03/2015,30/03/2015,0/01/1900,FALSE
11,2015004,"20,150,128,120,000","20,150,227,120,000",30,2.83,"2,306.19","2,306.19",28.01.2015,27.02.2015,28.01.2015,27.02.2015,"2,306.19",,2015,01,28,28/01/2015,28/01/2015,28/01/2015,0/01/1900,FALSE
11,2015002,"20,141,229,120,000","20,150,128,120,000",30,2.83,"2,306.19","2,306.19",29.12.2014,28.01.2015,29.12.2014,28.01.2015,"2,306.19",,2014,12,29,29/12/2014,29/12/2014,29/12/2014,0/01/1900,FALSE
11,2014013,"20,140,929,120,000","20,141,010,120,000",11,2.79,833.64971,833.64971,29.09.2014,10.10.2014,29.09.2014,10.10.2014,833.64971,,2014,09,29,29/09/2014,29/09/2014,29/09/2014,0/01/1900,FALSE
11,2014013,"20,141,010,120,000","20,141,229,120,000",80,2.79,"6,062.91","6,062.91",10.10.2014,29.12.2014,10.10.2014,29.12.2014,"6,062.91",,2014,10,10,10/10/2014,10/10/2014,10/10/2014,0/01/1900,FALSE
11,2014009,"20,140,630,120,000","20,140,929,120,000",91,2.755,"6,810.04","6,810.04",30.06.2014,29.09.2014,30.06.2014,29.09.2014,"6,810.04",,2014,06,30,30/06/2014,30/06/2014,30/06/2014,0/01/1900,FALSE
11,2014005,"20,140,328,120,000","20,140,630,120,000",94,2.74,"6,996.25","6,996.25",28.03.2014,30.06.2014,28.03.2014,30.06.2014,"6,996.25",,2014,03,28,28/03/2014,28/03/2014,28/03/2014,0/01/1900,FALSE
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,i think it took the hidden columns too.. :(
sorrysettlement table
CONTRACT_INT,CLOS_PER,DAYS_NUMB,LST track?,Calc from,Calc to
11,2016006,29,,29/02/2016,29/03/2016
11,2016004,32,,28/01/2016,28/01/2016
11,2016002,30,,29/12/2015,29/12/2015
11,2015024,29,,30/11/2015,30/11/2015
11,2015022,33,,28/10/2015,28/10/2015
11,2015020,30,,28/09/2015,28/09/2015
11,2015018,31,,28/08/2015,28/08/2015
11,2015016,31,,28/07/2015,28/07/2015
11,2015014,29,,29/06/2015,29/06/2015
11,2015012,32,,28/05/2015,28/05/2015
11,2015010,30,,28/04/2015,28/04/2015
11,2015008,31,,27/02/2015,27/02/2015
11,2015008,29,,30/03/2015,30/03/2015
11,2015004,30,,28/01/2015,28/01/2015
11,2015002,30,,29/12/2014,29/12/2014
11,2014013,11,,29/09/2014,29/09/2014
11,2014013,80,,10/10/2014,10/10/2014
11,2014009,91,,30/06/2014,30/06/2014
11,2014005,94,,28/03/2014,28/03/2014
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,lol, I wouldn't bother posting any more data in this forum. Your latest data still doesn't explain the CALCFROM and CALCTO fields as they still have way too many commas in it for a regular date field.
Try using the SUMIF, VLOOKUP and nested/multiple IF statements to see if does what you want.
bengal tiger yes re CALC_FROM and CALC_DATA - they are dates and times in SAP..
i have extracted and converted into our date time format in settlement table.i actually need like a nested vlookup - so 1st match the contract_ints and then settlement table.calcfrom and calc to should be within lst.valid from and valid_to
in my case i need to return a new column in settlement table for each row copy the relevant LST.repeat_per
which i can easily do in access.. but just out of curiosity can this be done in excel?
Nested VLOOKUPs are possible but it's generally for say Table 1 data looking up Table 3 data conditioned on Table 2 data. VLOOKUP will only retrieve data and won't be able to perform calculations within it. Date condition calculations need to be performed outside of the VLOOKUP function.
Nested IF is the best way to do it in Excel. The formula is rather messy but preferred as the alternative is creating dummy variable columns like you've done to the right columns in the Settlement data which then makes the data set messy. For example, try:
=IF(AND(OR(CALC_FROM>LST_DATE1,CALC_FROM<LST_DATE2),OR(CALC_TO>LST_DATE1,CALC_TO<LST_DATE2)),VLOOKUP(…),0)
i cant seem to work out the logic for nested vlookups
something like
if S.contract_int = LST.contrac_int and S.calc_from is greater than LST.calc_from and S.Calc_to< LST.calc_to then spit out the LST.repeat_per value.main question is can excel do this?
or is it access's job only?
Bengal tiger but wont this OR and AND function need reference to a cell..
i want to take each row of table 1 and loop thru all rows of table 2 to match 3 criteria (2 being dates)
which is what access can do.is that possible via excel.
i tried your suggestion of AND and OR functions but for cell A1 of table 1- it will only look up table2.row A's values
whereas i want it to keep looking until it finds a match in entire table2.hope that clears things up.
The IF(AND(OR())) statement only works for one row of Settlement data to check one row of LST data.
It is possible for one row of Settlement data to check every row of LST data, it can be done in two ways:
Option 1 - Macro in VBA - cleaner way to do it but requires programming knowledge
If you can program SQL queries in Access the jump isn't too big to Excel VBA. Say you have 20 rows of Settlement data and for each one you want it to check 10 rows of LST data. Code would go something like this:
Dim i, j as Integer
Dim Array(1 to 20, 1 to 10) as IntegerFor i = 1 to 20
For j = 1 to 10
If SETTLEMENT_ROW(i)_CALC_FROM >= LST_FROM And SETTLEMENT_ROW(i)_CALC FROM <= LST_TO
Then
If SETTLEMENT_ROW(i)_CALC_TO >= LST_FROM And SETTLEMENT_ROW(i)_CALC TO <= LST_TO
Then Array (i,j) = 1
Else Array(i,j) = 0
Else
Next j
Next iApologies for all that code being left-indented, the Ozbargain website won't let me indent each sub-loop.
This will cycle through the 10 LST records 20 times for each Settlement record. The Array variable acts as a binary identifier, "1" being if the Settlement date lies within the LST dates, "0" if it doesn't. The code above will only identify which records have met the condition, you'll need to write additional code for what you actually want to do with the data after this.
Option 2 - Array matrix in Excel spreadsheet to the right of the Settlement data - messier but more transparent.
If VBA/array data is too much for you then this is easier to visualise. For each record in Settlement, add an extra (say) 10 columns if you want it to match the LST records. Call it LST_DATE_1_OK to LST_DATE_10_OK, each cell will check if the date condition is met with my previous IF statement:
=IF(AND(OR(CALC_FROM>LST_DATE1,CALC_FROM<LST_DATE2),OR(CALC_TO>LST_DATE1,CALC_TO<LST_DATE2)),"Yes","No")
If the cell result returns "Yes" then the condition is met. It's easier to replace "Yes" with "1" and "No" with "0" in the IF statement so you can sum across the cell results and any total greater than zero indicates the condition is met.
Again this will only show you which records meet the LST conditions, the result will be the same array in the macro but visualised across a 20x10 table which will be more transparent for you.
I should note that creating a 20x10 array/table in either method is a little overkill as it'll check every LST record regardless, while you only want it to check each line until it returns a positive result. The code/functions can be refined but I'm a little short on time this Sunday to take this any further, I'll leave it to you/others if you want to refine this process to be a better fit for purpose but the overall methodology I've outlined will still get you what you want.
Thanks Bengal tiger - i will try option #2
=sum(http://www.excelforum.com/)