arranging data as per dates of each month
- Thread starter SANTHOSH
- Start date
SirJB7
Excel Rōnin
Hi, SANTHOSH!
If your source is worksheet Data and your wanted output is worksheet "Datewise List" which is the key field for sorting and grouping rows under the yellow rows dates? There's no field that evidently appears to be the argument required.
Despite of this I'd strongly recommend you to standardize the cell formats in 1st worksheet.
Regards!
If your source is worksheet Data and your wanted output is worksheet "Datewise List" which is the key field for sorting and grouping rows under the yellow rows dates? There's no field that evidently appears to be the argument required.
Despite of this I'd strongly recommend you to standardize the cell formats in 1st worksheet.
Regards!
@Sir JB7,
Thanks for responding to my query and it is my warm pleasure to meet such a nice person.
Though, I mentioned about the requirement early. it is briefly informed this complex data pertains to various projects of a single organization. Each day it is updated. Particulars of each vehicle is updated daily and monitored each month. This is the approved format of our company. Pls see the points below : -
For Insurance expiry : Column L5 to L 102 of Data worksheet to be sorted
For Road Tax Expiry : Column P5 to P 102 of Data worksheet to be sorted
for Goods tax Expiry : Column Q5 to Q 105 of Data worksheet to be sorted
For Fitness Expiry : Column T5 to Q 105 of Data worksheet to be sorted
and like that
Column reference has already indicated in "Datewise list"
This practice is doing /updating by manual data entry in "Datewise list" worksheet "from time to time. The total no columns are fixed in the "Datewise list" worksheet.
Rest already mentioned previously.
I am sure, I am taking little bit time of your goodselves but expect a lot to learn so much and try to be part of this forum.
SANTHOSH
Thanks for responding to my query and it is my warm pleasure to meet such a nice person.
Though, I mentioned about the requirement early. it is briefly informed this complex data pertains to various projects of a single organization. Each day it is updated. Particulars of each vehicle is updated daily and monitored each month. This is the approved format of our company. Pls see the points below : -
For Insurance expiry : Column L5 to L 102 of Data worksheet to be sorted
For Road Tax Expiry : Column P5 to P 102 of Data worksheet to be sorted
for Goods tax Expiry : Column Q5 to Q 105 of Data worksheet to be sorted
For Fitness Expiry : Column T5 to Q 105 of Data worksheet to be sorted
and like that
Column reference has already indicated in "Datewise list"
This practice is doing /updating by manual data entry in "Datewise list" worksheet "from time to time. The total no columns are fixed in the "Datewise list" worksheet.
Rest already mentioned previously.
I am sure, I am taking little bit time of your goodselves but expect a lot to learn so much and try to be part of this forum.
SANTHOSH
SirJB7
Excel Rōnin
Hi, SANTHOSH!
I read all previous posts and neither you answered clearly Somendra Misra's question about sorting of what date nor as you haven't done it now regarding my first post.
You must clearly specify:
a) What do you mean by sorting? Sort processes operate on a range of rows and columns and use keys that are whole columns. In your sample file it's impossible to say which whole column have you used for grouping the original data under each month yellow labels. Elaborate.
b) Inconsistent data. Feb-14 it's a joke? If not, explain the criteria used for those 3 records. Otherwise post a sample file without errors.
c) Full rows. I assume that when you posted the data you wanted in the output worksheet you tried to mean either row 102 or row 105 in all cases.
Regards!
PS: I appreciate and thank your words but if I were you I'd not bet on it...
Sorry for my late reply because stuck with some family issues. Please see the points below : -
The two worksheets given to you is exactly the live data and worksheet where I used to make data entry / update i.e, “Datewise” & “Data”.
a) What do you mean by sorting? Sort processes operate on a range of rows and columns and use keys that are whole columns. In your sample file it's impossible to say which whole column have you used for grouping the original data under each month yellow labels. Elaborate.
Sir, I mean to say sorting is “sorting of entire database with different expiry dates. Dates are most important and crucial. If it is a single column, it can be sorted easily in ascending or discending order. If it is more than one column, then result becomes little bit difficult. In the master data, if the data is sorted on a particular column say “Insurance Expiry” other expiry dates will not be in order. Each time sorting different columns one by one in the master data and get relevant data in the desired output will require lot of hectic practice and lot of headache. Please also see reply to point (C) you will came to know the procedure.
b) Inconsistent data. Feb-14 it's a joke? If not, explain the criteria used for those 3 records. Otherwise post a sample file without errors.
Here, this is an oversight from my side and please be excused. Sorting for the month of Feb 2014 is done except the data provided here. This data like 25/2, 28/2 is also expiry dates, which, comes from a different file. Surely, this can also be clubbed and analysed later on. So leave it for the time being.
c) Full rows. I assume that when you posted the data you wanted in the output worksheet you tried to mean either row 102 or row 105 in all cases.
Sir, the complete row is represented by the File No. like 102, 105, 147, etc. just like unique ID of an employee or CID No of a customer database in order to locate it easily.
For example, if the file no is named 102 and its name is TATA LPT 4135” and having 3 different expiry dates, in the output worksheet, the row containing File No., Name of equipment, RC No and expiry dates (having File No 102) will repeat 3 different times. Here, in the datewise list worksheet, total number of columns are 6, the file 102 will repeat maximum upto 6 times with difference in expiry dates. The expiry dates may be in same month or different month. If the data is summed up mothwise, it has to be shown separately
This is the procedure being followed by company and I do hope concept is now clear to you. Taking print outs in each and every day in A3 size pages is so difficult.
I do hope I have replied the points to your satisfaction and shall await for your some positive steps. I always ready to reply any of your queries,
Regards,
SANTHOSH
The two worksheets given to you is exactly the live data and worksheet where I used to make data entry / update i.e, “Datewise” & “Data”.
a) What do you mean by sorting? Sort processes operate on a range of rows and columns and use keys that are whole columns. In your sample file it's impossible to say which whole column have you used for grouping the original data under each month yellow labels. Elaborate.
Sir, I mean to say sorting is “sorting of entire database with different expiry dates. Dates are most important and crucial. If it is a single column, it can be sorted easily in ascending or discending order. If it is more than one column, then result becomes little bit difficult. In the master data, if the data is sorted on a particular column say “Insurance Expiry” other expiry dates will not be in order. Each time sorting different columns one by one in the master data and get relevant data in the desired output will require lot of hectic practice and lot of headache. Please also see reply to point (C) you will came to know the procedure.
b) Inconsistent data. Feb-14 it's a joke? If not, explain the criteria used for those 3 records. Otherwise post a sample file without errors.
Here, this is an oversight from my side and please be excused. Sorting for the month of Feb 2014 is done except the data provided here. This data like 25/2, 28/2 is also expiry dates, which, comes from a different file. Surely, this can also be clubbed and analysed later on. So leave it for the time being.
c) Full rows. I assume that when you posted the data you wanted in the output worksheet you tried to mean either row 102 or row 105 in all cases.
Sir, the complete row is represented by the File No. like 102, 105, 147, etc. just like unique ID of an employee or CID No of a customer database in order to locate it easily.
For example, if the file no is named 102 and its name is TATA LPT 4135” and having 3 different expiry dates, in the output worksheet, the row containing File No., Name of equipment, RC No and expiry dates (having File No 102) will repeat 3 different times. Here, in the datewise list worksheet, total number of columns are 6, the file 102 will repeat maximum upto 6 times with difference in expiry dates. The expiry dates may be in same month or different month. If the data is summed up mothwise, it has to be shown separately
This is the procedure being followed by company and I do hope concept is now clear to you. Taking print outs in each and every day in A3 size pages is so difficult.
I do hope I have replied the points to your satisfaction and shall await for your some positive steps. I always ready to reply any of your queries,
Regards,
SANTHOSH
SirJB7
Excel Rōnin
Hi, SANTOSH!
About this:
About this:
About this:
About this:
- column L, Expiry Date
- column P, Road Tax Paid Up To
- column Q, Goods Tax Paid Up To
- column R, Permit Up To
- column S, N Permit VAlid Up To
- column T, Fitness Up To
Questions:
1) From those the lower date will be considered for grouping task?
2) Or from what I partially understood of your last quoted paragraph (where you referred to a non existing case in the sample file, hence making things more difficult=, if you have a row with 6 different values in columns L,P,Q,R,S,T, it has to appear split in 6 rows in the output worksheet?
Regards!
About this:
Let us call it grouping by date values in several columns; keep sorting aside. And about being a database let us just call it a group of records with variable no. of attributes; keep database aside of Edgar F. Codd will try to turn in the grave and run away.
About this:
Rows not containing valid dates in the indicated columns will be grouped all together at the end (or at the beginning as I don't know how will they come out).
About this:
I was asking that because of this:
I'll assume the last row used, in sample file row 103.
About this:
I didn't not understand anything, should I do in order to analyze your requirement? I think I don't as I only need to know which columns are to be considered for the date grouping. Confirm that are the following, as you didn't specified them in your answer to a):
- column L, Expiry Date
- column P, Road Tax Paid Up To
- column Q, Goods Tax Paid Up To
- column R, Permit Up To
- column S, N Permit VAlid Up To
- column T, Fitness Up To
Questions:
1) From those the lower date will be considered for grouping task?
2) Or from what I partially understood of your last quoted paragraph (where you referred to a non existing case in the sample file, hence making things more difficult=, if you have a row with 6 different values in columns L,P,Q,R,S,T, it has to appear split in 6 rows in the output worksheet?
Regards!
Last edited:
SirJB7
Excel Rōnin
Hi, SANTOSH!
Well, had answered my previous questions besides liking Faseeh's comment it would have been better for you as you'd have helped me to aid you. So now I chose 2nd option.
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/arranging data as per dates of each month - List of Equipment 19-3-2014- RC (for SANTHOSH at chandoo.org).xlsm
This is the code:
A few comments:
1)
I know it might come from several sources, most probably copying & pasting, but you can copy & paste values only or use paste special, or anything that keeps a unique format. Despite of this the quality of the data is very poor (blank date fields, ok; NA, not ok; a value of 4; no way).
2)
3) I know you didn't say anything about it and you'll probably will when analyzing the automatic output, but I won't agree because of dates of year 1900 and other alike. If you don't know what I'm talking about, don't worry and discard this point.
Just advise if any issue. Please include the requested answers in your next post.
Regards!
Well, had answered my previous questions besides liking Faseeh's comment it would have been better for you as you'd have helped me to aid you. So now I chose 2nd option.
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/arranging data as per dates of each month - List of Equipment 19-3-2014- RC (for SANTHOSH at chandoo.org).xlsm
This is the code:
Code:
Option Explicit
Sub DontDoThatAgainFaseehOrIWillBillItToYou()
' constants
Const ksInputWS = "Data"
Const ksOutputWS = "Datewise List"
Const kiRowBlank = 10
Const kiRowTitle = 4
Const kiColumnLast = 25
Const kiColumnWitness = 2
Const ksColumnWitnessText = "Equipment"
Const ksColumnSource = "0 1 2 15 16 17 18 19 20 12 22"
Const ksColumnDate = "0 12 16 17 18 19 20"
Const ksFormat1 = "dd/mm/yyyy;@"
Const ksFormat2 = "mmm/yyyy;@"
Const kiFont = 18
Const kiColumnDate = 4
Const ksKey = "Key"
Const ksSlash = "/"
Const ksDot = "."
Const ksDash = "-"
' declarations
Dim wsI As Worksheet, wsO As Worksheet
Dim vSource As Variant, vDate As Variant
Dim dWork() As Date, bDate() As Boolean
Dim iSource As Integer, iDate As Integer, bOk As Boolean
Dim I As Long, J As Integer, K As Long, L As Integer, M As Integer
Dim A As String, V As Variant
' start
Set wsI = Worksheets(ksInputWS)
Set wsO = Worksheets(ksOutputWS)
vSource = Split(ksColumnSource)
vDate = Split(ksColumnDate)
iSource = UBound(vSource)
iDate = UBound(vDate)
ReDim dWork(iDate), bDate(iSource)
wsO.Cells.Clear
For I = 1 To iSource
For J = 1 To iDate
If vSource(I) = vDate(J) Then Exit For
Next J
bDate(I) = (J <= iDate)
Next I
' process
'
With wsI
' titles
K = 1
For I = 1 To iSource
wsO.Cells(K, I).Value = .Cells(kiRowTitle, Val(vSource(I))).Value
Next I
wsO.Cells(K, iSource + 1).Value = ksKey
' data
I = kiRowTitle + 1
J = 0
Do Until J > kiRowBlank Or I > .Rows.Count
A = .Cells(I, kiColumnWitness).Value
If A = "" Then
' count blanks
J = J + 1
Else
If A <> ksColumnWitnessText Then
' read input
J = 0
' dates
' load
For L = 1 To iDate
V = .Cells(I, Val(vDate(L))).Value
V = Replace(Replace(V, ksDot, ksSlash), ksDash, ksSlash)
On Error Resume Next
V = CDate(V)
On Error GoTo 0
If IsDate(V) Then
dWork(L) = V
Else
dWork(L) = CDate(0)
End If
Next L
' organize
For L = 1 To iDate
If dWork(L) <> CDate(0) Then
dWork(L) = dWork(L) - Day(dWork(L)) + 1
For M = L + 1 To iDate
If Year(dWork(L)) = Year(dWork(M)) And _
Month(dWork(L)) = Month(dWork(M)) Then _
dWork(M) = CDate(0)
Next M
End If
Next L
' write output
For L = 1 To iDate
If dWork(L) <> CDate(0) Then
K = K + 1
For M = 1 To iSource
V = Trim(.Cells(I, Val(vSource(M))).Value)
bOk = False
If Not bDate(M) Then
bOk = True
Else
If IsDate(V) Then
If Year(dWork(L)) = Year(V) And _
Month(dWork(L)) = Month(V) Then
bOk = True
V = CDate(V)
End If
End If
End If
If bOk Then wsO.Cells(K, M).Value = V
Next M
wsO.Cells(K, iSource + 1).Value = dWork(L)
End If
Next L
End If
End If
I = I + 1
Loop
End With
'
With wsO
' format
.Columns.AutoFit
With .Cells
.ClearFormats
.HorizontalAlignment = xlGeneral
End With
.Rows(1).Font.Bold = True
For I = 1 To iSource
If bDate(I) Then .Columns(I).NumberFormat = ksFormat1
Next I
.Columns(iSource + 1).NumberFormat = ksFormat1
' sort
With .Sort
With .SortFields
.Clear
.Add Key:=.Parent.Parent.Columns(iSource + 1), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.Add Key:=.Parent.Parent.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
End With
.SetRange .Parent.Cells
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' group
I = 2
Do While .Cells(I, iSource + 1).Value <> ""
If .Cells(I, iSource + 1).Value <> .Cells(I - 1, iSource + 1).Value Then
.Rows(I).Insert xlShiftDown
With .Cells(I, kiColumnDate)
.Value = .Parent.Cells(I + 1, iSource + 1).Value
.NumberFormat = ksFormat2
With .Font
.Size = kiFont
.Bold = True
End With
.HorizontalAlignment = xlCenter
End With
Range(.Cells(I, 1), .Cells(I, iSource + 1)).Interior.Color = vbYellow
I = I + 1
End If
I = I + 1
Loop
End With
'
' end
Set wsO = Nothing
Set wsI = Nothing
Beep
End SubA few comments:
1)
Let us call a bunch of data with almost no format at all.
I know it might come from several sources, most probably copying & pasting, but you can copy & paste values only or use paste special, or anything that keeps a unique format. Despite of this the quality of the data is very poor (blank date fields, ok; NA, not ok; a value of 4; no way).
2)
When you take your time to read this and described in detail but concisely how is the data updated in 1st worksheet, then may be you convince me to trigger the procedure execution at the worksheet change event code: it's a process that lasts a few seconds and it's not intended to be executed at any change. There are other possibilities but they'll depend on your answer.
3) I know you didn't say anything about it and you'll probably will when analyzing the automatic output, but I won't agree because of dates of year 1900 and other alike. If you don't know what I'm talking about, don't worry and discard this point.
Just advise if any issue. Please include the requested answers in your next post.
Regards!
SirJB7
Excel Rōnin
Hi, next missing guy!
Thank you a lot for your intercession, but surely it won't happen again, will it? If I ever happen to require your good offices I'll start a private conversation with you and tell you about.
Regards!
PS: Do you have plenty of aitchs and you don't know what to do with them? If I were you I wouldn't ask me...
SirJB7
Excel Rōnin
@Faseeh
Hi, next accidentallly injured buddy!
No news from your friend SANTOSH. I assume that's your friend as I wouldn't have started a conversation with both him and another person, asking this last one for assistance to the first one.
It seems as it's was neither urgent nor important, unless the testing process it's so but so hard and complex that the seven days of a whole week are not enough to do this job.
Regards!
Hi, next accidentallly injured buddy!
No news from your friend SANTOSH. I assume that's your friend as I wouldn't have started a conversation with both him and another person, asking this last one for assistance to the first one.
It seems as it's was neither urgent nor important, unless the testing process it's so but so hard and complex that the seven days of a whole week are not enough to do this job.
Regards!
Faseeh
Excel Ninja
@ SirJB7,
Well he was a forum member nothing special about him. i regret introducing him to your-goodself if you fell your time and energy has been wasted. Yes, he should be reverted (and at-least) updated what he was up-to. :? What can i say expect that i will think twice before refer such issues in future....
Well he was a forum member nothing special about him. i regret introducing him to your-goodself if you fell your time and energy has been wasted. Yes, he should be reverted (and at-least) updated what he was up-to. :? What can i say expect that i will think twice before refer such issues in future....
@ SIR JB7/Fasheesh,
How are you gentlemen, hope you are fine. Extremely sorry to give belated reply. There was no internet connectivity due to some networking / server issues. In fact not even in my town. Today only i have seen this message.
Please do not ensure that your energy has wasted. Primarily I understand there was so much problem in my data itself. When the question was typical, answer is also typical. I am not a hardcore expert in macro/ VB. Since it is a complex data, this function is to be tried and experimented on a test file many times before getting into practical.
Further, I have no wrong intention to put all the members into trouble but seek your help in solving the issues.
SANTHOSH
How are you gentlemen, hope you are fine. Extremely sorry to give belated reply. There was no internet connectivity due to some networking / server issues. In fact not even in my town. Today only i have seen this message.
Please do not ensure that your energy has wasted. Primarily I understand there was so much problem in my data itself. When the question was typical, answer is also typical. I am not a hardcore expert in macro/ VB. Since it is a complex data, this function is to be tried and experimented on a test file many times before getting into practical.
Further, I have no wrong intention to put all the members into trouble but seek your help in solving the issues.
SANTHOSH
@Dear JB Sir / Fasheesh,
I have seen the excel data, output data and the VB code in the file. I swear I am not a big hand to do such a big VB operation and even my fingers are trembling with fear. In which way I have to proceed ??? Really, is it a wonder to get data extracted through formula ? But, consider me as a fresher when I ask for any doubts. I never handled such complex data
Here are few observation :-
1. In the worksheet "Datewise list" Columns "Financed by" & " Key" not required. If the column named "Key" is essential, can it be hidden ?
2. Pls covert the file into xls format (2003 version) as I am not able to open the attached file properly.
3. As per my boss, Data before December 2012 is not required since the corresponding columns are empty.
Please bear with me till further message as it can be sorted out gradually only
SANTHOSH
I have seen the excel data, output data and the VB code in the file. I swear I am not a big hand to do such a big VB operation and even my fingers are trembling with fear. In which way I have to proceed ??? Really, is it a wonder to get data extracted through formula ? But, consider me as a fresher when I ask for any doubts. I never handled such complex data
Here are few observation :-
1. In the worksheet "Datewise list" Columns "Financed by" & " Key" not required. If the column named "Key" is essential, can it be hidden ?
2. Pls covert the file into xls format (2003 version) as I am not able to open the attached file properly.
3. As per my boss, Data before December 2012 is not required since the corresponding columns are empty.
Please bear with me till further message as it can be sorted out gradually only
SANTHOSH
SirJB7
Excel Rōnin
Hi, SANTOSH!
Obs. 1:
Your columns My columns
FILE NO. File. No.
EQUIPMENT Equipment
RC No. RC No.
ROAD TAX Road Tax Paid Up To
GOOD TAX Goods tax Paid up to
L.P Permit up to
N.P N. permit valid up-to.
FITNESS Fitness up to
INSURANCE Expiry Date
B.G Finance By
Key
As you might see the column headers that you posted doesn't match with the actual source column headers, so I tried to make my best efforts to find out which was each one. Up to "fitness" column H it seems to be all Ok. Your "insurance" and "b.g." were paired with "expiry" and "finance by", if that's not correct, do indicate which ones correspond (column letters, i.e., A, B, or so):
"key" is required by the code but you can hide it.
Obs. 2:
Below you'll find the links for both .xlsm and .xls version, but just in case you're not aware or this, you can download a converter from Microsoft that lets you handle not only Excel but all Office 2007+ files with newer extensions from 2003- program versions:
http://www.microsoft.com/en-us/download/dlx/listdetailsview.aspx?id=3
https://dl.dropboxusercontent.com/u...3-2014- RC (for SANTHOSH at chandoo.org).xlsm
https://dl.dropboxusercontent.com/u...-3-2014- RC (for SANTHOSH at chandoo.org).xls
Obs. 3:
Say to your boss the 3 famous Donald Trump's TV show words: You're fired!
Otherwise ask him where should I address the bill.
Now a little update/enhancement:
In the code section "'write output" I changed this:
by this:
so as to try to handle non standard formatted date cells (that are supposed to have a valid date value, but because of the annoying and absolutely improper source values for date columns aren't able to being directly considered as dates). Regarding this -which I see as the main and a major issue- you won't be never sure if you're including or classifying the information within the month that you should. Briefly: change or standardize your source data, or perhaps you'll be getting unpredictable results.
About what and how to do with the VBA code:
a) Worksheet "Datewise List Old": you can delete it since it was your sample output.
b) Worksheet "Datewise List": will be cleared and generated again each time you run the macro.
c) Worksheet "Data": your source data.
c) Macro to be run: Programmer/Developer tab, Code group, Macros button, select DontDoThatAgainFaseehOrIWillBillItToYou, Execute (same if Alt-F8 and then select and execute).
Regards!
Obs. 1:
Your columns My columns
FILE NO. File. No.
EQUIPMENT Equipment
RC No. RC No.
ROAD TAX Road Tax Paid Up To
GOOD TAX Goods tax Paid up to
L.P Permit up to
N.P N. permit valid up-to.
FITNESS Fitness up to
INSURANCE Expiry Date
B.G Finance By
Key
As you might see the column headers that you posted doesn't match with the actual source column headers, so I tried to make my best efforts to find out which was each one. Up to "fitness" column H it seems to be all Ok. Your "insurance" and "b.g." were paired with "expiry" and "finance by", if that's not correct, do indicate which ones correspond (column letters, i.e., A, B, or so):
"key" is required by the code but you can hide it.
Obs. 2:
Below you'll find the links for both .xlsm and .xls version, but just in case you're not aware or this, you can download a converter from Microsoft that lets you handle not only Excel but all Office 2007+ files with newer extensions from 2003- program versions:
http://www.microsoft.com/en-us/download/dlx/listdetailsview.aspx?id=3
https://dl.dropboxusercontent.com/u...3-2014- RC (for SANTHOSH at chandoo.org).xlsm
https://dl.dropboxusercontent.com/u...-3-2014- RC (for SANTHOSH at chandoo.org).xls
Obs. 3:
Say to your boss the 3 famous Donald Trump's TV show words: You're fired!
Otherwise ask him where should I address the bill.
Now a little update/enhancement:
In the code section "'write output" I changed this:
Code:
V = Trim(.Cells(I, Val(vSource(M))).Value)
Code:
V = Trim(.Cells(I, Val(vSource(M))).Value)
V = Replace(Replace(V, ksDot, ksSlash), ksDash, ksSlash)About what and how to do with the VBA code:
a) Worksheet "Datewise List Old": you can delete it since it was your sample output.
b) Worksheet "Datewise List": will be cleared and generated again each time you run the macro.
c) Worksheet "Data": your source data.
c) Macro to be run: Programmer/Developer tab, Code group, Macros button, select DontDoThatAgainFaseehOrIWillBillItToYou, Execute (same if Alt-F8 and then select and execute).
Regards!
@JBsir, No sir, Thanks for reminding. I am relaxed little bit that you replied politely. Actually I am little bit worried to reply. I have modified in the expiry dates of Insurance for testing purpose. That also in the file posted by you. at https://dl.dropboxusercontent.com/u...3-2014- RC (for SANTHOSH at chandoo.org).xls.
Sir, since it s a big vb code, I have to learn each and everything. dimensions, declare, etc. The file uploaded is the actual file already in operation with my boss. Can you suggest anything afresh for data? reason being it is being put into practice since a very long time and even boss thorough knowledge about it.
VDS
Sir, since it s a big vb code, I have to learn each and everything. dimensions, declare, etc. The file uploaded is the actual file already in operation with my boss. Can you suggest anything afresh for data? reason being it is being put into practice since a very long time and even boss thorough knowledge about it.
VDS
SirJB7
Excel Rōnin
Hi, SANTOSH!
About you change to the data, either if they're dates or anything else, the macro that creates the output, always regenerates it from scratch, so you can make the required changes and once you're done then you run the macro and the date wise worksheet is re-created.
About the code, you're facing the same paradigm of everyone that's trying to learn something new and before that learning process ends he has the chance to handle something beyond his actual skills. It's normal and we all go thru that situations every day (each time I download anything I don't know if I'll understand how it works, until I analyze it).
The paradigm is this: when you have something that works but you don't know how it works you have 3 options:
a) you don't use it until you can handle to manage the changes and modifications that it'd require in the future
b) you use it and intend (I hope that not dare) to use it and end the learning process while not being able to maintain it by yourself
c) you use it since you now understand the process
Which to choose? It depends on your confidence, if it's for personal or job use, if it supports a critical process, if you can get third parties support for it (neither from your environment nor from the author).
Most people who come here with an issue they go back to home/work with a working solution. Many of them understand as recently learned how to solve the problem (the "c" option), they don't take any risk as they're proficient with the new tool/method. Others (those of "a" and "b" options) have to decide by themselves whether they feel more comfortable. My personal opinion doesn't matter here, it's up to you to face the involved risks.
And in this case the major risk is the data source. Honestly it's of very poor quality, nothing standardized (field formats, fonts, cell contents, empty lines in the middle, ... ) and there is where you'd focus your efforts: ensure that the data you're putting into the raw data worksheet (Data) is clean and validated. Otherwise neither this nor the best workbook will do the job.
The actual data is a bit better than the original but at a first glance:
a) amounts columns have different no. of decimal places (amount and premium, I'm not talking of sum insured -yes, I know something about insurance-)
b) date columns content sucks -to be fully sincere-: dd/mm/yyyy, dd.mm.yy, "Exempted", dd.mm.yyyy, "N.A", "cancelled", "****"... nothing will work with that data zoo.
c) file no., column A, how could it be empty? I neither want nor need the answer, it's just a question that you might ask yourself or anybody else, but it's the main ID and it can't be blank; if no number assigned then create temporary numbers in a different range, i.e., starting at 1 million.
Hope it helps, because I think I won't be of further help unless you require a change in the specs of the project. The ball is now at your side of the field.
Regards!
About you change to the data, either if they're dates or anything else, the macro that creates the output, always regenerates it from scratch, so you can make the required changes and once you're done then you run the macro and the date wise worksheet is re-created.
About the code, you're facing the same paradigm of everyone that's trying to learn something new and before that learning process ends he has the chance to handle something beyond his actual skills. It's normal and we all go thru that situations every day (each time I download anything I don't know if I'll understand how it works, until I analyze it).
The paradigm is this: when you have something that works but you don't know how it works you have 3 options:
a) you don't use it until you can handle to manage the changes and modifications that it'd require in the future
b) you use it and intend (I hope that not dare) to use it and end the learning process while not being able to maintain it by yourself
c) you use it since you now understand the process
Which to choose? It depends on your confidence, if it's for personal or job use, if it supports a critical process, if you can get third parties support for it (neither from your environment nor from the author).
Most people who come here with an issue they go back to home/work with a working solution. Many of them understand as recently learned how to solve the problem (the "c" option), they don't take any risk as they're proficient with the new tool/method. Others (those of "a" and "b" options) have to decide by themselves whether they feel more comfortable. My personal opinion doesn't matter here, it's up to you to face the involved risks.
And in this case the major risk is the data source. Honestly it's of very poor quality, nothing standardized (field formats, fonts, cell contents, empty lines in the middle, ... ) and there is where you'd focus your efforts: ensure that the data you're putting into the raw data worksheet (Data) is clean and validated. Otherwise neither this nor the best workbook will do the job.
The actual data is a bit better than the original but at a first glance:
a) amounts columns have different no. of decimal places (amount and premium, I'm not talking of sum insured -yes, I know something about insurance-)
b) date columns content sucks -to be fully sincere-: dd/mm/yyyy, dd.mm.yy, "Exempted", dd.mm.yyyy, "N.A", "cancelled", "****"... nothing will work with that data zoo.
c) file no., column A, how could it be empty? I neither want nor need the answer, it's just a question that you might ask yourself or anybody else, but it's the main ID and it can't be blank; if no number assigned then create temporary numbers in a different range, i.e., starting at 1 million.
Hope it helps, because I think I won't be of further help unless you require a change in the specs of the project. The ball is now at your side of the field.
Regards!
@jb SIR,
I am so delighted to have such kind of reply from your end. I always believe for every type of problem, there is a solution. Basic concept of solving a big problem is nothing but to break into small parts and solving it gradually. Based on your valuable inputs I must definitely apply this concept in all walks of my life. As far as integration excel data is concerned, I never handled such a project first time in life during 10 year service. For the time being, the output may not be getting in the proper way due to reasons as explained above and always assume that the effort / every drop of energy taken by you is very much useful. I also helped to understand my gray areas as well. Since it is a complex data in excel, there are so much doubts coming in every day and it will be posted periodically. I do hope with active coordination and assistance from the team members, this forum is able to provide much through which I can also gain knowledge.
Have a wonderful day and great career ahead to all the members of this chandoo family.
SANTHOSH
I am so delighted to have such kind of reply from your end. I always believe for every type of problem, there is a solution. Basic concept of solving a big problem is nothing but to break into small parts and solving it gradually. Based on your valuable inputs I must definitely apply this concept in all walks of my life. As far as integration excel data is concerned, I never handled such a project first time in life during 10 year service. For the time being, the output may not be getting in the proper way due to reasons as explained above and always assume that the effort / every drop of energy taken by you is very much useful. I also helped to understand my gray areas as well. Since it is a complex data in excel, there are so much doubts coming in every day and it will be posted periodically. I do hope with active coordination and assistance from the team members, this forum is able to provide much through which I can also gain knowledge.
Have a wonderful day and great career ahead to all the members of this chandoo family.
SANTHOSH