FILTER inclucing merge cell

L

lucotuslim

Hi, I have below data

A B C D E
Row 1 PLACE CODE STATE CODE STATE
Row 2 USA CLIENT CLIENT COMPANY COMPANY
Row 3 2-10 SA 20-3 PO
Row 4 RUSSIA CLIENT CLIENT COMPANY COMPANY
Row 5 201 SR 90-1 OP

Please note that I had merge Cell A2 with A3, A4 with A5.

I applied autofilter but when I filtered PLACE with criteria USA, it only
shows
USA CLIENT CLIENT COMPANY COMPANY

Is there a way to show as below
USA CLIENT CLIENT COMPANY COMPANY
200 4000 200 4000
 
B

Bernie Deitrick

Lucotus,

Select all of your data table cells in column A, and use format cells to UN-Merge your cells. That
will split the cells, leaving the second row of each set of cells blank. So now you need to fill
those blank cells. Without changing your selection, use Edit / Go To... Special Blanks, then hit Ok.
Then type the equal sign, press the up arrow key once, and press CTrl-Enter. Then select all of
column A, copy it, and pastespecial values. Then when you filter your data set, you will get both
rows.

HTH,
Bernie
MS Excel MVP
 
L

lucotuslim

Thank. But is there anyway to do the filtering without un-filter the column
A, I need to maintain the Merge format.
 
B

Bernie Deitrick

Lucotus,

Use another column, where you in each of the two rows you reference the merged cell. Copy down to
match your data, then do the filter based on that new column.

In general, merged cells are a BAD idea, especially in data tables. OK for headers and reports, BAD
for data.

HTH,
Bernie
MS Excel MVP
 
L

lucotuslim

Cool
Thanks.
--
Lucotus


Bernie Deitrick said:
Lucotus,

Use another column, where you in each of the two rows you reference the merged cell. Copy down to
match your data, then do the filter based on that new column.

In general, merged cells are a BAD idea, especially in data tables. OK for headers and reports, BAD
for data.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
M

Matt

Hi Bernie

I have a similar problem which I hope you can help me with.

I have the following data:

A B C
1 PROJECT VALUE CLIENT
2 $10,000
3 NO 1 $1,000 ABC
4 DEF
5 GHJ
6 NO 2 $2,000 XYZ
7 ABC
8 NO 3 $3,000 DEF
9 LMN
10 NO 4 $4,000 ABC
11 QRS

The spreadsheet is intended to show the values of the quotes for Project
No's 1, 2, 3 & 4 which are being quoted to multiple clients.

Cells A3-A5, A6-A7, A8-A9 and A10-A11 are merged. Cells B3-B5, B6-B7, B8-B9
and B10-B11 are also merged. All cells in Column C are not merged.

I have a formula in cell B2 which reads =SUBTOTAL(9,B3:B65536). With the
table in it's unfiltered format (as above), this sums returns a value of
$10,000.

I have an autofilter on Row 2 that I would like to use to filter Column C
and see what projects we have quoted to Client ABC. The formula in cell B2
now returns a value of $5,000 which is incorrect. I would like this cell to
return a value of $7,000 ie including the contents of cell B6 (which is
merged with B7).

Is there another formula that I can use to total these values and/or can the
value which was originally entered into cell B6 appear in B7 without
increasing the total value of Column B in its unfiltered state.
 
D

Dave Peterson

Just a suggestion...

I think I'd drop all the merged cells and put each piece of information that I
need in each cell. I'd put the project id in each row. I'd put the value in
each row for each client.

It'll make filtering and subtotaling much easier.

And for me, I'd put the =subtotal() rows in row one and the titles in row 2. I
like that look better, but that's a personal preference.
 
B

Bernie Deitrick

Matt,

I would use a formula in cell D3:

=IF(B3<>"",B3,D2)

and copy that down to D4:D11.

Then do the subtotal based on column D, which would _only_ be used for filtering... so you could
change your SUBTOTAL formula to something like:

=if(COUNT(D3:D11)<>SUBTOTAL(2,D3:D11),SUBTOTAL(9,D3:D11),"")

to turn off the SUBTOTAL when filtering is not applied.

HTH,
Bernie
MS Excel MVP
 
Top