Duplicate & Zero Values in Lists (Excel 2003)

[ksp]

Hi All

I have a list of data in excel that is made up of alpha-numeric text
(job numbers), that has a second column to the right that has dollar
values. The list has blank lines within it. Similar to this

Column A Column B
abcd1234 123.50
wxyz8596 100.00

thed5555 150.00
huji7777 0.00

I have been able to use the array formula:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

to filter Column A to get rid of the blank rows. While I don't actually
follow how this formula works (I copied it from someone elses post) I
have successfully managed to filter column A and get rid of the blank
rows.

Now comes the but........

What I would really like to do is apply this to both columns. By this I
mean that I want to filter the data based on Column A and get rid of any
blank rows, but at the same time I want to keep the link to the dollar
value so that my resulting data would look like this

Column A Column B
abcd1234 123.50
wxyz8596 100.00
thed5555 150.00
huji7777 0.00

In addition to this I want to be able to discard the job numbers that
have a zero dollar value (this can be done in two steps if need be), so
that the ultimate result would look like this:

Column A Column B
abcd1234 123.50
wxyz8596 100.00
thed5555 150.00

I know - I don't want much !

Any suggestions / help would be appreciated

Many thanks in advance

Karen

 

[swatsp0p]

Have you looked into Auto-Filter? If not, Highlight your entire range
of data (include column headers) that you want to work with (say this
range is A1:B100, where A1 and B1 contain the headers "Job" and "Cost")
then click Tools>Filter>Auto Filter...

Note that the cells A1 and B1 now have down arrows in them. Click on
the A1 arrow and notice that all values contained within your range in
that column are listed. Scroll to the bottom and select "Non-Blanks".
All rows that are blank in Column A will be hidden. Now repeat this for
Column B, all rows that are blank in B will be hidden.

What is left is the data you want.

Is this what you are looking for?

Bruce

 

[Aladin Akyurek]

Your sample does not have any duplicate job numbers. If you had j8 twice
with dollar values of 5 and 7, I assume you want to list j8 once with 12
as its corresponding dollar value. Right?

Hi All

I have a list of data in excel that is made up of alpha-numeric text
(job numbers), that has a second column to the right that has dollar
values. The list has blank lines within it. Similar to this

Column A Column B
abcd1234 123.50
wxyz8596 100.00

thed5555 150.00
huji7777 0.00

I have been able to use the array formula:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

to filter Column A to get rid of the blank rows. While I don't actually
follow how this formula works (I copied it from someone elses post) I
have successfully managed to filter column A and get rid of the blank
rows.

Now comes the but........

What I would really like to do is apply this to both columns. By this I
mean that I want to filter the data based on Column A and get rid of any
blank rows, but at the same time I want to keep the link to the dollar
value so that my resulting data would look like this

Column A Column B
abcd1234 123.50
wxyz8596 100.00
thed5555 150.00
huji7777 0.00

In addition to this I want to be able to discard the job numbers that
have a zero dollar value (this can be done in two steps if need be), so
that the ultimate result would look like this:

Column A Column B
abcd1234 123.50
wxyz8596 100.00
thed5555 150.00

I know - I don't want much !

Any suggestions / help would be appreciated

Many thanks in advance

Karen

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[ksp]

Hi Bruce

Thanks for your input - not what I was thinking but this does get ri
of the blank rows and keep column A & B in tact

Any ideas on how to now get rid of the rows that have a zero cost valu
?

Ta

Kare

 

[ksp]

Hi Aladin

There should be no dupliate job numbers, but I stress the term should
So I have to assume that this may happen, so to answer your questio
Yes I would want to list the job once, but the cost would be th
consolidated amount / total

Ta

Kare

 

[Aladin Akyurek]

Let A2:B7 house the sample you provided:

{"Job#","Dollar
Value";"abcd1234",123.5;"wxyz8596",100;"","";"thed5555",150;"huji7777",0}

where A2 is Job#, B2 Dollar Value, A3 abcd1234, etc.

C1: 0

which is required.

C3, copied down:

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0))*(B3>0),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,"")

E1:

=LOOKUP(9.99999999999999E+307,C1:C7)

E3, copied down:

=IF(ROW()-ROW($E$3)+1<=$E$1,LOOKUP(ROW()-ROW($E$3)+1,$C$3:$C$7,$A$3:$A$7),"")

F3, copied down:

=IF(E3<>"",SUMIF($A$3:$A$7,E3,$B$3:$B$7),"")

The result area, E2:F5, now will show:

{"Job#","Dollar Value";"abcd1234",123.5;"wxyz8596",100;"thed5555",150}

 

[ksp]

Hi Aladin

Your formula's are a work of art ! and have solved my problem.

Thank-you for your help it is greatly appreciated

Regards

Karen