Excel help needed

**ITDWARRIOR** · 1st February 2011, 10:04 PM

Dear Friends mere pass ek file hai jis main 4 sheets hain.
1. All data
2. 15 days
3. 90 days
4. 180 days

main yeh chahta hon k Alla data wali sheet main jo IDs hain un IDs main jin ko 15 days se ziyada hogae hain or 30 days se kam hue hain woh automatically IDs 15 days wali sheet main ajaen. bilkul ese he jin IDs ko more than 90 days or less than 180 days hue hain woh 90 days wali sheet main ajaen. or jin ko more than 180 days hue hain woh 180 days wali sheet main ajaen.

File attach ki hui hai.
Note: logic lagany k liye ap koi bhi Row ya Column apni taraf se add ker sakty hain.

**sajjad_abid60** · 1st February 2011, 10:18 PM

i think ya possible nhe hay agar aysa koi formula hy to mary liy new ho ga

**ITDWARRIOR** · 1st February 2011, 10:30 PM

dear hona chahiye or hoga... Mujhe Syar bhai k reply ka w8 hai. he is genius in excel

**syar** · 2nd February 2011, 01:25 PM

Sallam::

Seee the attached file.Keya aap result ye he chahte hain.

regards

**ITDWARRIOR** · 2nd February 2011, 07:20 PM

syar said:

Sallam::

Seee the attached file.Keya aap result ye he chahte hain.

regards

w salam
syar bhai where is your attachment?
yahan tu koi attached file nahin hai apka.

**ITDWARRIOR** · 2nd February 2011, 08:51 PM

Salam

syar bhai maine thori koshish ki hai formula lagaya hai magar ek masla araha hai woh yeh k jo more than 14 days and less than 30 days wali IDs hain woh tu ajati hain magar jo more than 30 days wali IDs hain un columns main NA araha hai.. ab isko kaise khatam karon

sheet attach ker raha hon or ap mere ustad hain tu apny kam ka result bhi ap se maloom kerna pasand karonga.

or is file main maine sirf 15 days wali sheet per kam kiya hai.

Or maine Data wali sheet main days ko select all ker k us ko ek name days diya hai jo k condition main use kiya hai.

**syar** · 3rd February 2011, 10:19 AM

Sorry pehle file attached karna bhool gaya tha.

AAp ne jo vlookup ka formula lagaya ha is k saath aik masla ha. Vlookup hameesha wahan par value return karta ha jahan isto 1st match milta ha. 2nd match tak pohanchta he nahee. Is k leye aap ko array forumla's darkar ho ga jo k bohat costly parta ha.

Leekan before using/ trying array formula's ses the attached file agar aap result se agree hain tu is ka 3 step m asan hal ha.

**ITDWARRIOR** · 3rd February 2011, 01:56 PM

g han syar bhai mujhe yeh he result chahiye.

or syar bhai maine IF condition use ki hai matching k liye.. or vlookup tu just is liye use kiya hai taky jo ID match ho tu uska name bhi ajae.

ap apna tareqa bataiye jo ap keh rahy hain...

**syar** · 3rd February 2011, 03:51 PM

Follow these steps

1. Apni "Alldata" ki sheet pa apna data ko "DATA" k name se define karain.
2. apna 15 days ki sheet pa jain aur b1 aur c1 m apna criteria heading lekhain jaise aap ki all data ki sheet m ha "DAYS"(As per my attached sheet)
3. Apne condition b1 me lekhain jaise aap >15 chahte hain tu >15 lekh dain
C1 m <30 chahte hain tu <30 lekhain ( Jaise m ne lekhat ha.
4. DATA----> Advance filter-- choose karain.

Advance filter k menu m Copy to another location Select Karain.
List range m "DATA" Lekhain
Criteria Range m B1:C2 Select Karain
Copy TO m 15 days k sheet m aap jahan apna out put chahte hain single cell select kar k ok kar dain.
Aap ka jo data 15 se zayada aur 30 se kam hoga automatic aa jae ga.

Agar Aik dafa try karne se na ho tu 2,3 dafa try karin inshallah aap ka masla hal ho jae ga. Dosri sheets par bhee aap aise he try karain aap ka tamam condition hal ho jain ge.

Note: Jis tarah ki condition m ne yahan istimal ke ha is "AND" kahte hain jo aap ne apne IF logic m istiaml ki thee.

Jab aap samajh jain k aap advance filter pori tarah samajh gai hain tu mujhe reply karain m aap ko is to automate karne ka method batao ga k aik he click m aap ka sara kam ho gai. Mujh se screen shot nahee bante is leye maazrat

Regards

**ITDWARRIOR** · 3rd February 2011, 07:53 PM

Syar bhai advance filter per maine ap k bataey huey tareqy se kiya hai tu ho gaya hai.. mujhe iska automatic tareqa bataeye.. thanks

**syar** · 4th February 2011, 06:52 PM

aap is ko macro record karne se automatic kar sakte hain aap ko aik dafa macro record karne parhe gi aur future m aap sirf apna criteria lekhain ga aur macro run karain aap ka tamam steps automate ho gain ge. See the attache file for detail procedure.

Waise m chahta hon k aap macro par kam thoda seekh lain is leye ye procedure bata raha hon agar aap ko difficult lage tu phir formula par concentrate karainge. It's for your skill development in excel as advance user.

regards