Excel mavens ?

Showing 1-18 of 18 messages
Excel mavens ? GenesysWave 5/1/12 1:39 PM
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
Re: Excel mavens ? Andrew Beals 5/1/12 2:12 PM
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.

Re: Excel mavens ? Bill (hevnsnt @ i-hacked) 5/1/12 3:00 PM
I will second that python's csv library is amazing.
Re: Excel mavens ? Dave 5/1/12 5:00 PM
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
Re: Excel mavens ? Andrew Beals 5/1/12 5:23 PM

"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.
For more options, visit this group at http://groups.google.com/group/ccckc?hl=en.
Re: Excel mavens ? Andre F 5/1/12 11:12 PM
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.
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.

Re: Excel mavens ? GenesysWave 5/2/12 7:32 AM
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
Re: Excel mavens ? GenesysWave 5/2/12 7:33 AM
Annnnnnd python.org is blocked as well
Re: Excel mavens ? Bill (hevnsnt @ i-hacked) 5/2/12 7:38 AM
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."
Re: Excel mavens ? GenesysWave 5/2/12 7:42 AM

So does owing you a beer count as charging something?

Re: Excel mavens ? Bill (hevnsnt @ i-hacked) 5/2/12 7:45 AM
I think Zed would understand. :)  btw, if you are interested in Python, this book is awesome.  Another good one is  http://inventwithpython.com/ 
Re: Excel mavens ? GenesysWave 5/2/12 7:50 AM
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)
Re: Excel mavens ? Bill (hevnsnt @ i-hacked) 5/2/12 7:56 AM
hahah I promise there was no shenanigans in play here.
Re: Excel mavens ? Andrew Beals 5/2/12 8:06 AM
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.
Re: Excel mavens ? GenesysWave 5/2/12 8:08 AM
As long as that's true your beer will not smell of almonds - http://www.oedilf.com/db/Lim.php?Word=arsenic
Re: Excel mavens ? Andrew Beals 5/2/12 8:14 AM
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:

Re: Excel mavens ? GenesysWave 5/8/12 6:09 AM
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
Re: Excel mavens ? JTFlint 5/8/12 9:08 AM
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