Problem on filter with merging cell

[Blanko]

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:

 

[DushiPunda]

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]

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]

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

 

[Blanko]

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]

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:

	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.

 

[Blanko]

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]

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]

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]

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.