A | B | C | D | E | F | G | |
1 | Customer ID | Ticket Number | Status | Action 1 | Action 2 | Action 3 | Action 4 |
2 | 123456 | 102 | Pending | Test 1 | Test 2 | Test 3 | Test 4 |
3 | 321456 | 222 | Completed | Test 1 | Test 2 | Test 3 | Test 4 |
4 | 412356 | 415 | Pending | Test 1 | Test 2 | Test 3 | Test 4 |
A | B | C | D | |
1 | Customer ID | Ticket Number | Status | Action |
2 | 123456 | 102 | Pending | Test 1 Test 2 Test 3 Test 4 |
3 | 321456 | 222 | Completed | Test 1 Test 2 Test 3 Test 4 |
4 | 412356 | 415 | Pending | Test 1 Test 2 Test 3 Test 4 |
I'm not 100% sure, but I'd be willing to bet you're going to be able to do what you're trying to do with your current formatting. I would suggest something like this:
A
B
C
D
E
F
G
1
Customer ID
Ticket Number
Status
Action 1
Action 2
Action 3
Action 4
2
123456
102
Pending
Test 1
Test 2
Test 3
Test 4
3
321456
222
Completed
Test 1
Test 2
Test 3
Test 4
4
412356
415
Pending
Test 1
Test 2
Test 3
Test 4
<tbody>
</tbody>
Either something like that, or, while it will be pretty tedious to enter the data, you can keep the actions in one (unmerged) cell, like so:
A
B
C
D
1
Customer ID
Ticket Number
Status
Action
2
123456
102
Pending
Test 1
Test 2
Test 3
Test 4
3
321456
222
Completed
Test 1
Test 2
Test 3
Test 4
4
412356
415
Pending
Test 1
Test 2
Test 3
Test 4
<tbody>
</tbody>
The only other suggestion I can offer is to use a macro that will look through your data and copy the completed entries to another worksheet. Obviously this won't filter the actual data you are looking at...but it is, for all intents and purposes, a filter. Is that an option?
I am still new and learning on excel. Maybe is there a guide for macro? If using filter doesn't work... perhaps it will be good to try other method.
E | Formula | |
1 | Filter | N/A |
2 | Pending | =C2 |
3 | Pending | =IF(C3="",C2,C3) |
4 | Pending | =IF(C4="",C2,C4) |
5 | Pending | =IF(C5="",C2,C5) |
6 | Completed | =C6 |
7 | Completed | =IF(C7="",C6,C7) |
8 | Completed | =IF(C8="",C6,C8) |
9 | Completed | =IF(C9="",C6,C9) |
10 | Pending | =C10 |
11 | Pending | =IF(C11="",C10,C11) |
12 | Pending | =IF(C12="",C10,C12) |
13 | Pending | =IF(C13="",C10,C13) |
Using the data in the images you provided, I was able to do the following:
E
Formula
1
Filter
N/A
2
Pending
=C2
3
Pending
=IF(C3="",C2,C3)
4
Pending
=IF(C4="",C2,C4)
5
Pending
=IF(C5="",C2,C5)
6
Completed
=C6
7
Completed
=IF(C7="",C6,C7)
8
Completed
=IF(C8="",C6,C8)
9
Completed
=IF(C9="",C6,C9)
10
Pending
=C10
11
Pending
=IF(C11="",C10,C11)
12
Pending
=IF(C12="",C10,C12)
13
Pending
=IF(C13="",C10,C13)
<tbody>
</tbody>
I was then able to apply the same filter using the new column (E), and it showed everything it should show. It was very tedious to set up the formulas because you can't simply fill in the top formula and copy it down. You could do it for the 2nd/3rd/4th formula for each group by changing it to $C$2 (E3:E5), $C$6 (E7:E9), and $C$10 (E11:E13), respectively...but it would still be a tedious process to get it all set up.
As for the macro solution...my first question is - is every record set 4 rows? If that is true, then it should be relatively easy to do it. If it varies, then it'll be a little more challenging, but absolutely possible.
I know that you were only providing an example, and I figured your actual data set is much larger than what you've provided...thus, I agree, it would be very tedious. It was tedious enough for me with just your sample data.
In order to get started down the macro route - is there a column that is guaranteed to not be blank for every single record? In other words, is there a column that is guaranteed to have something in every single row?