How can I do this?

[Harvey Waxman]

Two source columns. A has unique labels and B has data
The columns have 200 rows

Target info is in C and D. Unique labels in C and data in D. Many more rows in
target than in source.

read A1
find corresponding value in column C. Let's say it was C30
copy value in B1 to D30.

Move on to A2 etc. until all source data has replaced corresponding data in
Column D.

Probably a macro, huh?

--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[Bob Greenblatt]

Two source columns. A has unique labels and B has data
The columns have 200 rows

Target info is in C and D. Unique labels in C and data in D. Many more rows
in
target than in source.

read A1
find corresponding value in column C. Let's say it was C30
copy value in B1 to D30.

Move on to A2 etc. until all source data has replaced corresponding data in
Column D.

Probably a macro, huh?

Sounds like a simple use for Vlookup.

Put this formula in D1:
=vlookup(c1,$a$1:$b$30,2)
and fill down in column D. After filling you may want to copy and paste
special values to remove the formulas.

 

[Harvey Waxman]

Put this formula in D1:
=vlookup(c1,$a$1:$b$30,2)
and fill down in column D. After filling you may want to copy and paste
special values to remove the formulas.

The problem I have is that D contains data that I don't want to change unless a
match in the source data is found. That is, if a label in Ax is a match to the
label in Cx, replace the data in Dx with the data in Bx, other wise leave it
alone.



In working with this set of data it would help to do the following:

count how many instances of the left three characters of a cell equals xyz in a
column. I thought that something like

countif(left(a1:a100,3)"=xyz")

would work but alas :-(. I'm sure there is some way to do this.

Thanks once again

Harvey


--
Harvey Products makers of Dinghy Dogs(TM)
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[Bob Greenblatt]

The problem I have is that D contains data that I don't want to change unless
a
match in the source data is found. That is, if a label in Ax is a match to
the
label in Cx, replace the data in Dx with the data in Bx, other wise leave it
alone.



In working with this set of data it would help to do the following:

count how many instances of the left three characters of a cell equals xyz in
a
column. I thought that something like

countif(left(a1:a100,3)"=xyz")

would work but alas :-(. I'm sure there is some way to do this.

Thanks once again

Harvey

Well, you can nest the vlookup inside a match to see if the value exists in
column A. Again, you may need a helper column to do this. For example:
=if(isna(match(c1,$a$1:$a$30,))),vlookup(...),d1)

You can do what you want with an array formula. Try array entering:

=sum(1*(left(a1:a300,3)="xyz"))

(I'm on vacation after tomorrow for 2 weeks. I'll do my best to follow
through with this till I leave.)

 

[Harvey Waxman]

You can do what you want with an array formula. Try array entering:

=sum(1*(left(a1:a300,3)="xyz"))

That works.

1. why does it need the 1* since 1 times anything doesn't change the value ?
2. why won't countif do the same thing?

I'm just a "why"ner

Thanks and have a great vacation.

Recommend a basic function explanation source?


--
Harvey Products makers of Dinghy Dogs(TM)
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

In working with this set of data it would help to do the following:

count how many instances of the left three characters of a cell equals xyz in
a
column. I thought that something like

countif(left(a1:a100,3)"=xyz")

would work but alas :-(. I'm sure there is some way to do this.

Try

=COUNTIF(A1:A100,"xyz*")

 

[JE McGimpsey]

=sum(1*(left(a1:a300,3)="xyz"))

That works.

1. why does it need the 1* since 1 times anything doesn't change the value ?[/QUOTE]

SUM ignores non-numeric values. The comparison returns a boolean array.
Math operators coerce booleans to 1/0 (TRUE/FALSE). You could multiply
by 1, add 0 or use two unary minuses.

Could also use SUMPRODUCT():

=SUMPRODUCT(--(LEFT(A1:A100,3)="xyz"))

2. why won't countif do the same thing?

COUNTIF requires a range, not an array of values (like LEFT(A1:A100,3)
returns). However, you can use the wild card functionality:

=COUNTIF(A1:A100,"xyz*")

 

[Harvey Waxman]

I'm sure there is some way to do this.

Try

=COUNTIF(A1:A100,"xyz*")

Way too easy!

Thanks

--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[Bob Greenblatt]

That works.

1. why does it need the 1* since 1 times anything doesn't change the value ?
2. why won't countif do the same thing?

I'm just a "why"ner

Thanks and have a great vacation.

Recommend a basic function explanation source?

Harvey,


The left(a1:a300)="xyz" returns an array of 300 TRUE,FALSE values.
Multiplying this array coerces the true to 1 and the false to zero. The sum
then adds the results.

Many of the Excel books on the market explain array functions. The best are
probably by John Walkenbach. Check out a few at Borders or Barnes & Nobel
and pick one that you like. The Windows explanations for this area of excel
are virtually 100% the same for Macintosh.

 

[Harvey Waxman]

The left(a1:a300)="xyz" returns an array of 300 TRUE,FALSE values.
Multiplying this array coerces the true to 1 and the false to zero. The sum
then adds the results.

Good, plain explanation. Is the recommended book as good as you?


--
Harvey Products makers of Dinghy Dogs(TM)
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me