Multiple conditions

J

jlkirk

Active Member
Joined
May 6, 2002
Messages
328
Office Version
  1. 365
In column A I have various accounts labeled Account A, Account B, Account C, Account D, and Account E, over 250 in number, not in any particular order. In Column B, I have money deposits corresponding to the various accounts listed by dates (the dates in column C). In column D I have text (>50 letters/words) that contain the words "Dividends", "Interest", and "Capital Gains", located at random places within the text.

What I would like to do is list each deposit in each account by the name in column B, by date, in columns D, E, and F. For example, in column D I would like to list the dividends received in account, by date, each dividend deposit, each interest and each capital gains deposit.

Any help?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Something Like this:


Book1
ABCDEFGH
1AccountDepositDateText
2B132024-02-03xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 10.002024-01-28Dividends
3E172024-02-08xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 20.002024-01-31Interest
4A172024-02-04xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 12.002024-01-31Capital Gains
5E152024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 17.002024-02-04Interest
6D112024-02-04xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqA$ 20.002024-02-11Dividends
7D102024-02-05xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 14.002024-02-12Capital Gains
8C202024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 19.002024-02-13Dividends
9D192024-02-06xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqB$ 17.002024-01-31Dividends
10A202024-02-11xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqB$ 13.002024-02-03Interest
11E142024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqB$ 17.002024-02-07Dividends
12D102024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqB$ 14.002024-02-10Dividends
13B142024-02-10xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqC$ 18.002024-01-26Interest
14A142024-02-12xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 20.002024-02-07Interest
15A202024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqC$ 19.002024-02-07Capital Gains
16C192024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 16.002024-02-13Capital Gains
17C162024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 16.002024-02-13Capital Gains
18C162024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqD$ 11.002024-02-04Dividends
19A192024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 10.002024-02-05Interest
20A102024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 15.002024-02-05Capital Gains
21B172024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 19.002024-02-06Capital Gains
22B172024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 10.002024-02-13Capital Gains
23E172024-02-11xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 15.002024-01-28Interest
24C182024-01-26xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqE$ 14.002024-01-28Interest
25D152024-02-05xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 17.002024-02-08Interest
26A122024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 17.002024-02-11Capital Gains
Sheet11
Cell Formulas
RangeFormula
E2:H26E2=LET(Data1,$A$2:$C$26,Desc,$D$2:$D$26,Type,IFS(ISNUMBER(SEARCH("Interest",Desc)),"Interest",ISNUMBER(SEARCH("Dividends",Desc)),"Dividends",ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains",1,""),NewData,HSTACK(Data1,Type),AcctName,CHOOSECOLS(NewData,1),TranDate,CHOOSECOLS(NewData,3),SORTBY(NewData,AcctName,1,TranDate,1))
Dynamic array formulas.
 
Upvote 0
awoohaw said:
Something Like this:


Book1
ABCDEFGH
1AccountDepositDateText
2B132024-02-03xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 10.002024-01-28Dividends
3E172024-02-08xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 20.002024-01-31Interest
4A172024-02-04xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 12.002024-01-31Capital Gains
5E152024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 17.002024-02-04Interest
6D112024-02-04xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqA$ 20.002024-02-11Dividends
7D102024-02-05xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 14.002024-02-12Capital Gains
8C202024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqA$ 19.002024-02-13Dividends
9D192024-02-06xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqB$ 17.002024-01-31Dividends
10A202024-02-11xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqB$ 13.002024-02-03Interest
11E142024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqB$ 17.002024-02-07Dividends
12D102024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqB$ 14.002024-02-10Dividends
13B142024-02-10xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqC$ 18.002024-01-26Interest
14A142024-02-12xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 20.002024-02-07Interest
15A202024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqC$ 19.002024-02-07Capital Gains
16C192024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 16.002024-02-13Capital Gains
17C162024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqC$ 16.002024-02-13Capital Gains
18C162024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqD$ 11.002024-02-04Dividends
19A192024-02-13xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 10.002024-02-05Interest
20A102024-01-28xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 15.002024-02-05Capital Gains
21B172024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 19.002024-02-06Capital Gains
22B172024-02-07xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcDividendslmnopqlmnopqD$ 10.002024-02-13Capital Gains
23E172024-02-11xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 15.002024-01-28Interest
24C182024-01-26xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcInterestlmnopqlmnopqE$ 14.002024-01-28Interest
25D152024-02-05xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 17.002024-02-08Interest
26A122024-01-31xyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcxyxabcCapital GainslmnopqlmnopqE$ 17.002024-02-11Capital Gains
Sheet11
Cell Formulas
RangeFormula
E2:H26E2=LET(Data1,$A$2:$C$26,Desc,$D$2:$D$26,Type,IFS(ISNUMBER(SEARCH("Interest",Desc)),"Interest",ISNUMBER(SEARCH("Dividends",Desc)),"Dividends",ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains",1,""),NewData,HSTACK(Data1,Type),AcctName,CHOOSECOLS(NewData,1),TranDate,CHOOSECOLS(NewData,3),SORTBY(NewData,AcctName,1,TranDate,1))
Dynamic array formulas.
Click to expand...
Excellent! Works Perfectly; thanks
 
Upvote 0
it is sorted in the LET() function. You can change that in ways you like. I thought I sorted it as you asked in the OP.
 
Upvote 0
awoohaw said:
it is sorted in the LET() function. You can change that in ways you like. I thought I sorted it as you asked in the OP.
Click to expand...
You did, absolutely. Its just that I might want to sort differently in the future
 
Upvote 0
Then I suggest using a helper column and using the regular AUTO-FILTER tool
This would be the formula for the helper column.
Excel Formula: 
=LET(
Desc,$D$2:$D$26,
Type,
IFS(
ISNUMBER(SEARCH("Interest",Desc)),"Interest",
ISNUMBER(SEARCH("Dividends",Desc)),"Dividends",
ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains",
1,""),
Type)

NOTE: I'm free forming this, so it may need tweaking.
 
Upvote 0
awoohaw said:
Then I suggest using a helper column and using the regular AUTO-FILTER tool
This would be the formula for the helper column.
Excel Formula: 
=LET(
Data1,$A$2:$C$26,
Desc,$D$2:$D$26,
Type,
IFS(
ISNUMBER(SEARCH("Interest",Desc)),"Interest",
ISNUMBER(SEARCH("Dividends",Desc)),"Dividends",
ISNUMBER(SEARCH("Capital Gains",Desc)),"Capital Gains",1,""),
Type)

NOTE: I'm free forming this, so it may need tweaking.
Click to expand...
Thanks again...Ill let you know
 
Upvote 0
jlkirk said:
Thanks again...Ill let you know
Click to expand...
works great.

Let me give you another: Suppose I want to match the account in column A with the text in column D. For example, I want column E to list the Dividends from Account A on any particular date. I think it has to do with the SUMIFS function with an accompanying AND function, but I cant figure out how to parse out the matching language (in this case "Dividend") from column D.

Here is as far as I got: Column E=Sumifs(And(A:A="Account A"),(D:D="*Dividends*"),B:B), but it doesnt seem to work.
Any ideas?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs
V
  • Question Question
interest calculations
Replies
2
Views
325
vsuram
V
S
  • Question Question
Splitting text into only two cells ?
Replies
4
Views
438
Peter_SSs
Peter_SSs
S
  • Question Question
VBA Code that extracts multiple tabs at a time based on lookup tab
2
Replies
10
Views
470
NoSparks
N
A
  • Solved
lookup question
Replies
3
Views
146
ajm
A

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

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