Problem on filter with merging cell

Blanko

New Member
Joined
Jun 8, 2017
Messages
12
Excel version: 2010

Currently having problem on showing all my data. The screenshot below shows my objective of filtering 'Completed'. However, there are action that are being cut and only shows the 1st row. My objective is to show all the data when being filter accordingly.


1:



2:
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

DushiPunda

Well-known Member
Joined
Nov 14, 2015
Messages
508
Excel version: 2010

Currently having problem on showing all my data. The screenshot below shows my objective of filtering 'Completed'. However, there are action that are being cut and only shows the 1st row. My objective is to show all the data when being filter accordingly.


1:



2:

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>
 

Blanko

New Member
Joined
Jun 8, 2017
Messages
12
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>

Hi, thanks for the reply. However because it is a example of how my company store the data so I could not change the way my company does.
 

DushiPunda

Well-known Member
Joined
Nov 14, 2015
Messages
508
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?

Edit: Are you able to add additional columns? You may be able to implement something similar to the solution provided on this post:

https://social.technet.microsoft.co...ged-and-non-merged-columns-in-one?forum=excel
 
Last edited:

Blanko

New Member
Joined
Jun 8, 2017
Messages
12

ADVERTISEMENT

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.
 

DushiPunda

Well-known Member
Joined
Nov 14, 2015
Messages
508
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.

Using the data in the images you provided, I was able to do the following:

EFormula
1FilterN/A
2Pending=C2
3Pending=IF(C3="",C2,C3)
4Pending=IF(C4="",C2,C4)
5Pending=IF(C5="",C2,C5)
6Completed=C6
7Completed=IF(C7="",C6,C7)
8Completed=IF(C8="",C6,C8)
9Completed=IF(C9="",C6,C9)
10Pending=C10
11Pending=IF(C11="",C10,C11)
12Pending=IF(C12="",C10,C12)
13Pending=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.
 
Last edited:

Blanko

New Member
Joined
Jun 8, 2017
Messages
12

ADVERTISEMENT

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.

That was just an example. In my actual excel, it doesn't have fix number of 'Action'. It can be more than 15-20 rows. Furthermore, either than a few columns, there are actually many more. So putting the formula will be very tedious for me.
 

DushiPunda

Well-known Member
Joined
Nov 14, 2015
Messages
508
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?
 

Blanko

New Member
Joined
Jun 8, 2017
Messages
12
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?


Yes there is.
 

DushiPunda

Well-known Member
Joined
Nov 14, 2015
Messages
508
Which column is that? Also, I should have specified in my previous post that the column needs to not have merged cells. As you've found out by trying to filter with merged cells, only the first (top left) cell in a range of merged cells holds a value.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,268
Messages
5,657,745
Members
418,411
Latest member
Excellency

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
Top