staticfluids
New Member
- Joined
- Apr 24, 2024
- Messages
- 8
- Office Version
- 365
- 2021
- 2019
- 2016
- Platform
- Windows
- Mobile
- Web
Hi, first of all, sorry for my bad English.
I have been looking for a solution here, trial and error by myself, and the Microsoft forum as well. But haven't found any solution that satisfies the needs. I think it's possible but I don't know how to put it into Excel formula.
The logic is like this:
FILTER --> if found "THE CRITERIA DATA" OR "THE SECOND CRITERIA DATA", return the value from column 1 until column 8 --> if the value from the column 3 is not "THE CRITERIA DATA" OR "THE SECOND CRITERIA DATA", return the value to blank or ""
I have data like this from cell A2 to H55 (the original data is a table, but i put the sample only):
I wanted to put the dynamic formula in another cell so it updates accordingly with its original rows.
I tried to use the FILTER formula at cell J2, the output is like this:
The filtered data is correct, but what I want is like this:
this is using simple IF formula plus the criteria and then i have to drag it all down from S2 until Z55
My question is, is it possible to use a dynamic formula like FILTER to put the desired output like the expected result?
It seems possible but until now i haven't found the solution yet. Me personally think it has something to do with IFERROR/MAP/LAMBDA/CHOOSECOLS/CHOOSEROW/VSTACK/HSTACK formula.
Thank you all, have a blessed day
I have been looking for a solution here, trial and error by myself, and the Microsoft forum as well. But haven't found any solution that satisfies the needs. I think it's possible but I don't know how to put it into Excel formula.
The logic is like this:
FILTER --> if found "THE CRITERIA DATA" OR "THE SECOND CRITERIA DATA", return the value from column 1 until column 8 --> if the value from the column 3 is not "THE CRITERIA DATA" OR "THE SECOND CRITERIA DATA", return the value to blank or ""
I have data like this from cell A2 to H55 (the original data is a table, but i put the sample only):
| Row | Date | Contents | Amount | Tier | Done | Periode | Remarks |
2 | 26 March 2022 | Other Tier 0 #1 | Rp 11,400,000.00 | 0 | Yes | 01 March 2022 | |
3 | 26 March 2022 | Other Tier 6 #1 | Rp 620,000.00 | 6 | No | 01 March 2022 | |
4 | 26 March 2022 | Other Tier 6 #2 | Rp 270,000.00 | 6 | No | 01 March 2022 | |
5 | 26 March 2022 | Other Tier 6 #3 | Rp 60,000.00 | 6 | Recheck | 01 March 2022 | |
6 | 26 March 2022 | Other Tier 5 #1 | Rp 250,000.00 | 5 | Yes | 01 March 2022 | |
7 | 05 May 2022 | Other Tier 0 #4 | Rp 100,000.00 | 1 | Yes | 01 March 2022 | |
8 | 22 April 2022 | THE CRITERIA DATA | Rp 500,000.00 | 1 | Yes | 01 March 2022 | |
9 | 01 April 2022 | THE CRITERIA DATA | Rp 400,000.00 | 1 | Yes | 01 March 2022 | |
10 | 01 April 2022 | THE SECOND CRITERIA DATA | Rp 700,000.00 | 1 | Yes | 01 March 2022 | |
11 | 13 March 2022 | THE SECOND CRITERIA DATA | Rp 300,000.00 | 1 | Yes | 01 March 2022 | |
12 | 26 March 2022 | Other Tier 1 #1 | Rp 200,000.00 | 1 | Yes | 01 March 2022 | |
13 | 26 March 2022 | Other Tier 1 #2 | Rp 500,000.00 | 1 | Yes | 01 March 2022 | |
14 | 26 March 2022 | Other Tier 2 #1 | Rp 100,000.00 | 2 | Recheck | 01 March 2022 | |
15 | 26 March 2022 | Other Tier 2 #2 | Rp 200,000.00 | 2 | Recheck | 01 March 2022 | |
16 | 26 March 2022 | Other Tier 2 #3 | Rp 2,070,000.00 | 2 | Yes | 01 March 2022 | |
17 | 26 March 2022 | Other Tier 2 #4 | Rp 225,000.00 | 2 | Yes | 01 March 2022 | |
18 | 26 March 2022 | Other Tier 3 #1 | Rp 50,000.00 | 3 | Yes | 01 March 2022 | |
19 | 26 March 2022 | Other Tier 3 #2 | Rp 85,000.00 | 3 | Yes | 01 March 2022 | |
20 | 26 March 2022 | Other Tier 3 #3 | Rp 350,000.00 | 3 | Yes | 01 March 2022 | |
21 | 26 March 2022 | Other Tier 3 #4 | Rp 275,000.00 | 3 | Yes | 01 March 2022 | |
22 | 26 March 2022 | Other Tier 3 #5 | Rp 150,000.00 | 3 | Yes | 01 March 2022 | |
23 | 26 March 2022 | Other Tier 3 #6 | Rp 2,500,000.00 | 3 | Yes | 01 March 2022 | |
24 | 26 March 2022 | Other Tier 3 #7 | Rp 150,000.00 | 3 | Yes | 01 March 2022 | |
25 | 26 March 2022 | Other Tier 3 #8 | Rp 52,000.00 | 3 | Yes | 01 March 2022 | |
26 | 26 March 2022 | Other Tier 3 #9 | Rp 1,355,000.00 | 3 | Yes | 01 March 2022 | |
27 | 26 February 2022 | Other Tier 0 #2 | Rp 10,620,000.00 | 0 | Yes | 01 February 2022 | |
28 | 26 February 2022 | Other Tier 6 #4 | Rp 510,000.00 | 6 | No | 01 February 2022 | |
29 | 26 February 2022 | Other Tier 6 #5 | Rp 260,000.00 | 6 | No | 01 February 2022 | |
30 | 26 February 2022 | Other Tier 6 #6 | Rp 60,000.00 | 6 | Recheck | 01 February 2022 | |
31 | 13 March 2022 | THE CRITERIA DATA | Rp 1,000,000.00 | 1 | Yes | 01 February 2022 | |
32 | 28 February 2022 | THE CRITERIA DATA | Rp 1,000,000.00 | 1 | Yes | 01 February 2022 | |
33 | 13 March 2022 | THE SECOND CRITERIA DATA | Rp 700,000.00 | 1 | Yes | 01 February 2022 | |
34 | 28 February 2022 | THE SECOND CRITERIA DATA | Rp 1,000,000.00 | 1 | Yes | 01 February 2022 | |
35 | 11 February 2022 | THE SECOND CRITERIA DATA | Rp 100,000.00 | 1 | Yes | 01 February 2022 | |
36 | 26 January 2022 | THE SECOND CRITERIA DATA | Rp 200,000.00 | 1 | Yes | 01 February 2022 | |
37 | 26 February 2022 | Other Tier 1 #3 | Rp 1,500,000.00 | 1 | Yes | 01 February 2022 | |
38 | 26 February 2022 | Other Tier 2 #5 | Rp 200,000.00 | 2 | Recheck | 01 February 2022 | |
39 | 26 February 2022 | Other Tier 2 #6 | Rp 200,000.00 | 2 | Recheck | 01 February 2022 | |
40 | 26 February 2022 | Other Tier 2 #7 | Rp 1,930,000.00 | 2 | Yes | 01 February 2022 | |
41 | 26 February 2022 | Other Tier 3 #10 | Rp 250,000.00 | 3 | Yes | 01 February 2022 | |
42 | 26 February 2022 | Other Tier 3 #11 | Rp 175,000.00 | 3 | Yes | 01 February 2022 | |
43 | 26 February 2022 | Other Tier 3 #12 | Rp 100,000.00 | 3 | Yes | 01 February 2022 | |
44 | 26 February 2022 | Other Tier 3 #13 | Rp 350,000.00 | 3 | Yes | 01 February 2022 | |
45 | 26 February 2022 | Other Tier 3 #14 | Rp 40,000.00 | 3 | Yes | 01 February 2022 | |
46 | 26 February 2022 | Other Tier 3 #15 | Rp 100,000.00 | 3 | Yes | 01 February 2022 | |
47 | 26 February 2022 | Other Tier 3 #16 | Rp 200,000.00 | 3 | Yes | 01 February 2022 | |
48 | 26 February 2022 | Other Tier 3 #17 | Rp 40,000.00 | 3 | Yes | 01 February 2022 | |
49 | 26 February 2022 | Other Tier 3 #18 | Rp 105,000.00 | 3 | Yes | 01 February 2022 | |
50 | 26 February 2022 | Other Tier 3 #19 | Rp 110,000.00 | 3 | Yes | 01 February 2022 | |
51 | 26 January 2022 | Other Tier 0 #3 | Rp 3,175,000.00 | 0 | Yes | 01 January 2022 | |
52 | 26 January 2022 | Other Tier 6 #7 | Rp - | 6 | No | 01 January 2022 | |
53 | 26 January 2022 | Other Tier 6 #8 | Rp 80,000.00 | 6 | No | 01 January 2022 | |
54 | 26 January 2022 | Other Tier 6 #9 | Rp 20,000.00 | 6 | Recheck | 01 January 2022 | |
55 | 26 January 2022 | Other Tier 3 #20 | Rp 3,075,000.00 | 3 | Yes | 01 January 2022 |
I wanted to put the dynamic formula in another cell so it updates accordingly with its original rows.
I tried to use the FILTER formula at cell J2, the output is like this:
Excel Formula:
=FILTER(A2:H55,(C2:C55 = "THE CRITERIA DATA") + (C2:C55 = "THE SECOND CRITERIA DATA"))| Row | Date | Contents | Amount | Tier | Done | Periode | Remarks |
8 | 22-Apr-22 | THE CRITERIA DATA | Rp 500,000.00 | 1 | Yes | 01-Mar-22 | |
9 | 01-Apr-22 | THE CRITERIA DATA | Rp 400,000.00 | 1 | Yes | 01-Mar-22 | |
10 | 01-Apr-22 | THE SECOND CRITERIA DATA | Rp 700,000.00 | 1 | Yes | 01-Mar-22 | |
11 | 13-Mar-22 | THE SECOND CRITERIA DATA | Rp 300,000.00 | 1 | Yes | 01-Mar-22 | |
31 | 13-Mar-22 | THE CRITERIA DATA | Rp 1,000,000.00 | 1 | Yes | 01-Feb-22 | |
32 | 28-Feb-22 | THE CRITERIA DATA | Rp 1,000,000.00 | 1 | Yes | 01-Feb-22 | |
33 | 13-Mar-22 | THE SECOND CRITERIA DATA | Rp 700,000.00 | 1 | Yes | 01-Feb-22 | |
34 | 28-Feb-22 | THE SECOND CRITERIA DATA | Rp 1,000,000.00 | 1 | Yes | 01-Feb-22 | |
35 | 11-Feb-22 | THE SECOND CRITERIA DATA | Rp 100,000.00 | 1 | Yes | 01-Feb-22 | |
36 | 26-Jan-22 | THE SECOND CRITERIA DATA | Rp 200,000.00 | 1 | Yes | 01-Feb-22 |
The filtered data is correct, but what I want is like this:
this is using simple IF formula plus the criteria and then i have to drag it all down from S2 until Z55
Excel Formula:
=IF(OR($C2="THE CRITERIA DATA",$C2="THE SECOND CRITERIA DATA"),A2,"")| Row | Date | Contents | Amount | Tier | Done | Periode | Remarks |
8 | 22-Apr-22 | THE CRITERIA DATA | Rp 500,000.00 | 1 | Yes | 01-Mar-22 | |
9 | 01-Apr-22 | THE CRITERIA DATA | Rp 400,000.00 | 1 | Yes | 01-Mar-22 | |
10 | 01-Apr-22 | THE SECOND CRITERIA DATA | Rp 700,000.00 | 1 | Yes | 01-Mar-22 | |
11 | 13-Mar-22 | THE SECOND CRITERIA DATA | Rp 300,000.00 | 1 | Yes | 01-Mar-22 | |
31 | 13-Mar-22 | THE CRITERIA DATA | Rp 1,000,000.00 | 1 | Yes | 01-Feb-22 | |
32 | 28-Feb-22 | THE CRITERIA DATA | Rp 1,000,000.00 | 1 | Yes | 01-Feb-22 | |
33 | 13-Mar-22 | THE SECOND CRITERIA DATA | Rp 700,000.00 | 1 | Yes | 01-Feb-22 | |
34 | 28-Feb-22 | THE SECOND CRITERIA DATA | Rp 1,000,000.00 | 1 | Yes | 01-Feb-22 | |
35 | 11-Feb-22 | THE SECOND CRITERIA DATA | Rp 100,000.00 | 1 | Yes | 01-Feb-22 | |
36 | 26-Jan-22 | THE SECOND CRITERIA DATA | Rp 200,000.00 | 1 | Yes | 01-Feb-22 | |
My question is, is it possible to use a dynamic formula like FILTER to put the desired output like the expected result?
It seems possible but until now i haven't found the solution yet. Me personally think it has something to do with IFERROR/MAP/LAMBDA/CHOOSECOLS/CHOOSEROW/VSTACK/HSTACK formula.
Thank you all, have a blessed day
