Unique Records issue

R

Ramy

Hello,
I'm having some trouble formatting my spreadsheet. I have a column tha
is full of customer part numbers. This row has dupilcate numbers in it
Underneath most of these numbers is a blank cell (up to four deep). Th
blank cells correspond to pretinent information in adjacent cell
(rows). How can I return all Unique records INCLUDING the blank cell
corresponding to each one (all blank cells in that column)?

Ram
 
D

Dave Peterson

You may want to try this against a copy of your worksheet--just in case!

First, I'm assuming that column A contains the customer part numbers.

And the data looks something like this (starting in row 2 with headers in row
1):

aaa $B$2 $C$2 $D$2 $E$2 $F$2 $G$2
$B$3 $C$3 $D$3 $E$3 $F$3 $G$3
$B$4 $C$4 $D$4 $E$4 $F$4 $G$4
bb $B$5 $C$5 $D$5 $E$5 $F$5 $G$5
$B$6 $C$6 $D$6 $E$6 $F$6 $G$6
$B$7 $C$7 $D$7 $E$7 $F$7 $G$7
$B$8 $C$8 $D$8 $E$8 $F$8 $G$8
aaa $B$9 $C$9 $D$9 $E$9 $F$9 $G$9
$B$10 $C$10 $D$10 $E$10 $F$10 $G$10
$B$11 $C$11 $D$11 $E$11 $F$11 $G$11
$B$12 $C$12 $D$12 $E$12 $F$12 $G$12
dd $B$13 $C$13 $D$13 $E$13 $F$13 $G$13
$B$14 $C$14 $D$14 $E$14 $F$14 $G$14
$B$15 $C$15 $D$15 $E$15 $F$15 $G$15
$B$16 $C$16 $D$16 $E$16 $F$16 $G$16

I'd insert a new column A and put a formula like this in A2 and copy down:

=IF(B2="",A1,IF(COUNTIF($B$2:$B2,B2)=1,"ShowIt","HideIt"))

I got this (starting in row 2):

ShowIt aaa $B$2 $C$2 $D$2 $E$2 $F$2 $G$2
ShowIt $B$3 $C$3 $D$3 $E$3 $F$3 $G$3
ShowIt $B$4 $C$4 $D$4 $E$4 $F$4 $G$4
ShowIt bb $B$5 $C$5 $D$5 $E$5 $F$5 $G$5
ShowIt $B$6 $C$6 $D$6 $E$6 $F$6 $G$6
ShowIt $B$7 $C$7 $D$7 $E$7 $F$7 $G$7
ShowIt $B$8 $C$8 $D$8 $E$8 $F$8 $G$8
HideIt aaa $B$9 $C$9 $D$9 $E$9 $F$9 $G$9
HideIt $B$10 $C$10 $D$10 $E$10 $F$10 $G$10
HideIt $B$11 $C$11 $D$11 $E$11 $F$11 $G$11
HideIt $B$12 $C$12 $D$12 $E$12 $F$12 $G$12
ShowIt dd $B$13 $C$13 $D$13 $E$13 $F$13 $G$13
ShowIt $B$14 $C$14 $D$14 $E$14 $F$14 $G$14
ShowIt $B$15 $C$15 $D$15 $E$15 $F$15 $G$15
ShowIt $B$16 $C$16 $D$16 $E$16 $F$16 $G$16

Then I could apply Data|filter|autofilter to column A and just show the
showit's.

Did you really mean you wanted to include all the blanks, too? For those
Hideit's under the second aaa should be shown, too.

I'm guessing no.
 
Top