Excel function Help(Urgent)

S

sk5567

New Member
Joined
Apr 10, 2003
Messages
4
I have a Excel sheet,which contains 2 sheets namely Sheet1 and Sheet2.

Sheet1 contains 2 columns namely activity code and Start Date.The data looks like this

Sheet1:

Activity code Date
P1 01/31/2003
P2 03/31/2003
p1 12/31/2002
p2 04/01/2003

In sheet1 The activity code repeats.

Sheet2 also contains the same 2 columns.The data will be like this

Activity code Date
P1
P2

Now my requirement is For P1 in 2nd sheet I have to get the min date for P1 from first sheet .ie, For p1 I have to goto sheet1 and check all the dates for P1 and get minimum date for p1.

The value will be 12/31/2002.

How to do this?? Any function or we need to write a macro?? I don't know VBA programming.Please help

Thanks
sk5567
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Book3
ABCD
1ActivitycodeDate
2P11/31/2003
3P23/31/2003
4p112/31/2002
5p24/1/2003
6
Sheet1
Book3
ABCD
1
2p112/31/2002
3
Sheet2


The formula is...

=MIN(IF((Sheet1!$A$2:$A$5=A2),Sheet1!$B$2:$B$5))

which must be array-entered by hitting control+shift+enter at the same time (not just enter).
 
Upvote 0
Not functionoing properly??

Hi Aladin,

Thanks for your reply. I have used the solution given by you.

It is giving the min date of all dates. It is not taking only for P1.It is considering P2,p3 also while getting the min value.

How I want is If I pass P1,it should get minimum date from P1 only.

Any Idea??

Thanks
sk5567
 
Upvote 0
Re: Not functionoing properly??

sk5567 said:
...It is giving the min date of all dates. It is not taking only for P1.It is considering P2,p3 also while getting the min value.

How I want is If I pass P1,it should get minimum date from P1 only.

Any Idea??...
Click to expand...

Did you hit control+shift+enter at the same time to enter the formula?
 
Upvote 0
Working Fine

Hi Aladin

Excellent. It is working Fine.Thanks alot.

But I have 1 more question for you.

Similarly I have 2 sheets.

Sheet 1 contains

Activity code Status
P1 In Progress
P2 Completed
P3 In Progress
P1 completed
P2 in progress
P3 Yet to start


Sheet 2 contains

Activity code Status
P1
P2
P3

Now I have to get the values for P1,P2,P3 from sheet1.

The scenario is like this

If all are yet to start I have to return Yetto start

If all are completed I have to return completed

If any one is In Progress I have to return In Progress.

For Example, For P1 I have In Progress and Completed.In this case I have to return In Progress.

Any Help??
Once again thanks for the great solution.

Thanks
sk5567
 
Upvote 0
Re: Working Fine
aaCountAndEvaluate sk5567.xls
ABCD
1ActivitycodeStatus
2P1InProgress
3P2Completed
4P3Yettostart
5P1completed
6P2inprogress
7P3Yettostart
8
Sheet1
aaCountAndEvaluate sk5567.xls
ABCDEFG
1CompletedInProgressYettoStartFreqActivitycodeStatus
21102P1InProgress
31102P2InProgress
40022P3YettoStart
5
Sheet2


As you see, additional columns are used to obtain desired results.

Formulas are...

A1:

=SUMPRODUCT((Sheet1!$A$2:$A$7=$E2)*(Sheet1!$B$2:$B$7=A$1))

which is copied acrooss then down.

D1:

=COUNTIF(Sheet1!$A$2:$A$7,$E2)

F2:

=IF(B2,$B$1,IF(A2=$D2,$A$1,IF(C2=$D2,$C$1,"")))
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
191
zero269
Z
H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
191
harmless92
H
L
  • Solved
How to get Login/Logout Times for users with multiple login/logouts on the multiple days
Replies
3
Views
206
leenvr
L
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
259
B-radK
B
D
  • Solved
Use the same macro on a weekly file which contains date info
Replies
1
Views
274
John_w
J

Forum statistics

Threads
1,215,517
Messages
6,125,290
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top