• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Particular Format i want Data

Abhijeet

Active Member
Hi

I have data from that I want from Asg Num Column each Number If Any code mention in same Row & If any code mention then that date I want as start Date then where R is mention then before 1 day that date I want as End Date.

2.If Any code is not given Only R is mention then that date I want as start date

3.If only Code is given & R is not mention then 1st Code Date I want as Start Date

4.Each Number if any Comment is mention then that comment & File name I want

I have mention in Expected Result sheet what type of data I want
 

Attachments

  • Sickness 8.xlsx
    14.3 KB · Views: 0
I looked at the file before I posted. It's not clear which circumstances should cause which action.
 

Hi Abhijeet !

Since more than 600 posts you have done, you got all needed to start
your own code ! In this case, there is no difficulty even for a beginner,
just needs some easy logic …
 
Hi Mark L

I am trying to do this but problem is how to identify which Code is mention in given range & how to pull Start date & End date If R is mention. From 1St to 31St days if 3 times R is mention then how to pull the data this is problem for me if u can give me idea how to do this then i will try to design macro
 
I have data of Sickness from each Asg Num that is mention in Column G if Any Code mention in any days that start date & end date i want If R is mention before 1 day that is end date.

Days are mention in Cell L1:AP1 Month is mention in Column I

If Mar-15 is mention in month column from 1st to 5th S14 code is mention & R is mention in 7th Day then Start date is 1-Mar2015 & End Date is 6-Mar-2015 & Code S14

If From starting days Only R is mention then i want that day is in Start date
For This type of example Month column Mar-15 is mention & R is mention in 15th day then i want in start date as 15-mar-2015 & code should be R(Actually R means Return to work This is not Code but for my understanding i want mention in Code)

This is sickness data when emp goes on sickness & when return to work this is data so i want each code what is start date & end date i want
 
Last edited:

If you don't ever start to write your code skeleton,
at least write a technical analysis to achieve it …​
 


Not sure of what you mean but for example with VBA Range.End property …​
 
Last edited:
Hi Marc L

IF Month mention Apr-2015

From 2day to 7th day S14 code mention & 10th day R is mention Then i want Start Date is 2-Apr-2015 & End Date is 9-Apr-2015

R means Return to Work
 

No such data in your Expected result worksheet neither in Data source !

But no matters with Range.End property :
easy to find out last cell from a continuous data area.
And with the first cell of previous Column (Range property as well)
you'll get the date …
 

Try this : place cursor on cell L3 and hit Ctrl + Right Arrow keys …
Activate Macro recorder and redo the same and see the code !

If you succeed to understand that, read again my previous posts
and see VBA help …
 
Hi Marc L

I understood that but my question is how to Find R where ever mention in each Row If From range L2:AP2 3 times mention i.e(7th day next is on 15th day next is on 25th) then how to identify 3 end date of each Sickness i.e(1st End Date is 6-Apr2015 2nd End Date is 14-Apr-2015 & 3rd End Date is 24-Apr-2015)

How to identify this End Date
 
I have do this how many R is mention in given range
Sub LastUsedRow_Find()
'FIND method to determine Last Row with Data, in a worksheet

Dim lastRow As Long
Dim rng As Range

Set rng = Range("L2:Ap10")

lastRow = rng.Find(What:="R", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

MsgBox lastRow

End Sub
 
Hi Narayan Sir

Data is upto 700 rows If you can give formula based then its take time to give result
So VBA solution is good i think
 

I should not use Find method on "R" 'cause some lines have not a "R".
As yet written, I prefer COUNTA worksheet function
combined with code generated like in post #18 …

So start to write your code skeleton to read data line by line.
Based on your sample workbook, code will be tiny, less than 30 lines.
But the question is : all possible cases are they in this sample ?

'Cause I'll post a one shot demonstration, in case of you certainly forgot
to mention something you'll amend it to your needs, I won't …
 
Hi Marc L

Attach sample file in post #1 all possible cases in that can u please give me code as per this file
 

I'm still waiting for your analysis or even your code skeleton … :rolleyes:

I'll meet again a 10-11 years old schoolchildren class,
last time they succeed to use an advanced filter and generate a code,
I'll ask them to analyse your beginner level problem (in terms of logic) …​
 
Last edited:
Back
Top