|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
Which I know is not right
As always your assistance is greatly appreciated.
|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.
|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.
|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.
|Re: Excel mavens ?||Andre F||5/1/12 11:12 PM|
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)
Attached is my test file.
|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|
|Re: Excel mavens ?||Bill (hevnsnt @ i-hacked)||5/2/12 7:38 AM|
Before anyone gets all huffy about me sharing, from the document:
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|
|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|
|Re: Excel mavens ?||GenesysWave||5/2/12 8:08 AM|
|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.
or, as I like to do (which should also work)
Quidquid latine dictum sit, altum sonatur.
- Whatever is said in Latin sounds profound.