Excel mavens ?

13 views
Skip to first unread message

James Costello

unread,
May 1, 2012, 4:39:05 PM5/1/12
to ccckc
All right for all the Excel magicians out there.
I am trying to run a formula to move around some values between two data sets so that I can eliminate some duplicate entries in a address entry list
What I need to do
Compare the value in column D to all of the values in Column H
When they match I need to take the value in Column G that corresponds to Column H match and place it in Column E
The values for Columns G and H are much shorter than the values in Column D

This is what I have so far
=IF(ISNA(MATCH($D5,H:H,TRUE))=FALSE,G:G,"")
Which I know is not right

As always your assistance is greatly appreciated.
Thanks
James

Andrew Beals

unread,
May 1, 2012, 5:12:32 PM5/1/12
to cc...@googlegroups.com
1. Export it as csv
2. Write your program in Python, as it has an excellent csv library, and output the results as csv
3. Import program output back in to Excel

Or, you could write a really nasty nested IF that does what you want in column E, and you probably want to do MATCH($D5,H:H,0) for an exact match, unless you're a big fan of inverted logic.




James

--
You received this message because you are subscribed to the Google Groups "CCCKC" group.
To post to this group, send email to cc...@googlegroups.com.
To unsubscribe from this group, send email to ccckc+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/ccckc?hl=en.

hevnsnt

unread,
May 1, 2012, 6:00:52 PM5/1/12
to cc...@googlegroups.com
I will second that python's csv library is amazing.

Dave

unread,
May 1, 2012, 8:00:59 PM5/1/12
to cc...@googlegroups.com
Or you can use perl and DBD::CSV to treat the CSV as a table and use SQL to extract your data, then simply vomit the results back out to disk.

Dave


On Tuesday, May 1, 2012 5:00:52 PM UTC-5, Bill (hevnsnt @ i-hacked) wrote:
I will second that python's csv library is amazing.

On Tue, May 1, 2012 at 4:12 PM, Andrew Beals <andrew...@gmail.com> wrote:
1. Export it as csv
2. Write your program in Python, as it has an excellent csv library, and output the results as csv
3. Import program output back in to Excel

Or, you could write a really nasty nested IF that does what you want in column E, and you probably want to do MATCH($D5,H:H,0) for an exact match, unless you're a big fan of inverted logic.
On Tue, May 1, 2012 at 3:39 PM, James Costello <genes...@gmail.com> wrote:
All right for all the Excel magicians out there.
I am trying to run a formula to move around some values between two data sets so that I can eliminate some duplicate entries in a address entry list
What I need to do
Compare the value in column D to all of the values in Column H
When they match I need to take the value in Column G that corresponds to Column H match and place it in Column E
The values for Columns G and H are much shorter than the values in Column D

This is what I have so far
=IF(ISNA(MATCH($D5,H:H,TRUE))=FALSE,G:G,"")
Which I know is not right

As always your assistance is greatly appreciated.
Thanks

James

--
You received this message because you are subscribed to the Google Groups "CCCKC" group.
To post to this group, send email to cc...@googlegroups.com.
To unsubscribe from this group, send email to ccckc+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/ccckc?hl=en.

--
You received this message because you are subscribed to the Google Groups "CCCKC" group.
To post to this group, send email to cc...@googlegroups.com.
To unsubscribe from this group, send email to ccckc+unsubscribe@googlegroups.com.

Andrew Beals

unread,
May 1, 2012, 8:23:15 PM5/1/12
to cc...@googlegroups.com

"perl" and "puke" in the same sentence. Coincidence? I think not.

To view this discussion on the web visit https://groups.google.com/d/msg/ccckc/-/rjA-Lhwms6YJ.

To post to this group, send email to cc...@googlegroups.com.
To unsubscribe from this group, send email to ccckc+un...@googlegroups.com.

Andre

unread,
May 2, 2012, 2:12:31 AM5/2/12
to cc...@googlegroups.com
James, 
If I understood correctly, here's what you need to do:
1. Copy Column G into Column I
2. Set Column E as follows: -
E1 = VLOOKUP(D1,H:I,2,FALSE)
E2 = VLOOKUP(D2,H:I,2,FALSE) 
...etc

Attached is my test file.



James

--
You received this message because you are subscribed to the Google Groups "CCCKC" group.
To post to this group, send email to cc...@googlegroups.com.
Book1.xls

James Costello

unread,
May 2, 2012, 10:32:02 AM5/2/12
to cc...@googlegroups.com
I was going to be a smart ass and use PowerShell since
1. I am just starting on python
2. Powershell is native on my Win7 box
 but my clients proxies are blocking most of the powershell reference sites
I'll shut up and switch to python now

James Costello

unread,
May 2, 2012, 10:33:43 AM5/2/12
to cc...@googlegroups.com
Annnnnnd python.org is blocked as well

hevnsnt

unread,
May 2, 2012, 10:38:47 AM5/2/12
to cc...@googlegroups.com
LearnPythonTheHardWay2ndEdition.pdf

Before anyone gets all huffy about me sharing, from the document:

"License
This book is Copyright (C) 2010 by Zed A. Shaw. You are free to distribute this book to anyone you
want, so long as you do not charge anything for it, and it is not altered. You must give away the book in
its entirety, or not at all. This means it’s alright for you to teach a class using the book, so long as you
aren’t charging students for the book and you give them the whole book unmodified."

James Costello

unread,
May 2, 2012, 10:42:17 AM5/2/12
to cc...@googlegroups.com

So does owing you a beer count as charging something?

hevnsnt

unread,
May 2, 2012, 10:45:50 AM5/2/12
to cc...@googlegroups.com
I think Zed would understand. :)  btw, if you are interested in Python, this book is awesome.  Another good one is  http://inventwithpython.com/ 

James Costello

unread,
May 2, 2012, 10:50:30 AM5/2/12
to cc...@googlegroups.com
ah damn it, I opened a pdf that bill had control of
When will I learn
(goes to physically destroy system with a therm... like substance)

hevnsnt

unread,
May 2, 2012, 10:56:03 AM5/2/12
to cc...@googlegroups.com
hahah I promise there was no shenanigans in play here.

Andrew Beals

unread,
May 2, 2012, 11:06:08 AM5/2/12
to cc...@googlegroups.com
And for those of you who haven't shipped shrink-wrapped FORTH-language projects:

http://thinking-forth.sourceforge.net/

Free PDF e-book, multiple formats.  It will expand your mind.

James Costello

unread,
May 2, 2012, 11:08:44 AM5/2/12
to cc...@googlegroups.com
As long as that's true your beer will not smell of almonds - http://www.oedilf.com/db/Lim.php?Word=arsenic

Andrew Beals

unread,
May 2, 2012, 11:14:43 AM5/2/12
to cc...@googlegroups.com
Having column D be all the same value certainly makes it easier to copy one cell and paste it into a ton of others.  Excel seems to have lost SuperCalc's "Copy Special", which allowed you to decide which reference values stayed absolute and which were relative references.  (123 had it as well, of course.  I presume that VisiCalc had it back in the day)

On Wed, May 2, 2012 at 1:12 AM, Andre <andr...@gmail.com> wrote:

James Costello

unread,
May 8, 2012, 9:09:56 AM5/8/12
to cc...@googlegroups.com
so ended up doing the following
using the following to compare
=VLOOKUP(D2,I1:J114,2,0) and pull over the data I needed

Thanks for the suggesstions

Walter Acheson

unread,
May 8, 2012, 12:08:29 PM5/8/12
to cc...@googlegroups.com
In case I didn't misunderstand what you were looking for...

$ = absolute.
a1 = relative
a$1 = relative column, absolute row.
$a1 = absolute column, relative row.
$a$1 = absolute cell.
and this applies to copy and fill down scenarios.

=VLOOKUP(D2,$I$1:$J$114,2,0)

or, as I like to do (which should also work)
=VLOOKUP(D2,I:J,2,0)
--
Quidquid latine dictum sit, altum sonatur.
- Whatever is said in Latin sounds profound.
http://sites.google.com/site/walteracheson/
Walter Acheson
Reply all
Reply to author
Forward
0 new messages