| View previous topic :: View next topic |
| Author |
Message |
sheeblasta89
Guest
|
 Posted: Wed Sep 09, 2009 7:44 pm Post subject: Transfer data from one excel to another |
 |
|
I need to transfer data from excel to another excel based off of a common variable. Column A, the common variable is a list of patient names about 200. Column B is a list of numbers that needs to be matched up with the other excel to the patients name. There are some multiple rows of the same name bc of end/restart dates that need to be shown. This isn't that important I can fix this myself. Also there isn't a match for every single number from colum B. I am completly new with script making. Also the rows involved don't start at 1 and aren't A/B with the 2nd sheet. I can give examples of the sheets if needed.
my e-mail is athurman815@gmail.com but I will be checking this site regularly.[/code] |
|
| Back to top |
|
 |
sheeblasta
Joined: 09 Sep 2009
Posts: 5
|
| Posted: Wed Sep 09, 2009 8:35 pm Post subject: |
|
|
| I know that MS excel 2007 have these capabilitys but at work I only have excel 2000, once i get the picture of how to transfer data based on the variable I have huge potential in lowering my work load. I have made my own COM scripts that start programs and commands within the browser but I depend too much on the help file and recorder. |
|
| Back to top |
|
|
Leef_me
Joined: 08 Apr 2009
Posts: 5336
Location: San Diego, California
|
| Posted: Wed Sep 09, 2009 9:54 pm Post subject: |
|
|
| Quote: | | but I will be checking this site regularly.[/ |
Now that you've registered, you can set up your profile to watch a thread and get emails when someone posts 
Have you talked to your boss about this? "patient information" has been in the news, and not in a good way 
have you tried searching the forum for related posts?
also, I use site:autohotkey.com excel in a google search and get a lot of responses.
Using the recorder can be more than just a crutch if you use it right.
I use Excel a lot, but probably less mouse movements than the average person. When I first learned to use a computer, there were no mice!
To do special things like save a file and copy a block of text I learned hotkeys, or the Alt-F-S equivalents.
That said, you can use F5 to open the "goto" dialog box and enter any cell address.
If you name regions of the spreadsheet, you can select whole blocks at a time. If you use F5 to goto a named range, it selects all those cells.
If you hit ctl-c the text of the selected cells is placed on the clipboard, and you can copy the data to a program variable.
| Code: | | f:=clipboard ;<===== for example |
Example #3 shows how to parse the data on the clipboard http://www.autohotkey.com/docs/commands/LoopParse.htm
 Just think what wonderful things you could do if you could get the data from selected columns of an excel spreadsheet into AHK variables
Here's a link that talks more about excel ranges, maybe other tricks as well.
http://www.accountingweb.co.uk/item/29393 |
|
| Back to top |
|
|
sheeblasta
Joined: 09 Sep 2009
Posts: 5
|
| Posted: Thu Sep 10, 2009 6:09 am Post subject: |
|
|
| I am just using patient last name with their weekly hours. Its just a billing sheet. I want to take the exported data off of quickbooks and enter it into the billing sheet. |
|
| Back to top |
|
|
ruespe
Joined: 17 Jun 2008
Posts: 243
|
| Posted: Thu Sep 10, 2009 12:16 pm Post subject: |
|
|
Do you know the Excel-function VLOOKUP? It's a MUST for every Excel-User.
_________________
Greetings
Rog |
|
| Back to top |
|
|
ahklerner
Joined: 26 Jun 2006
Posts: 1381
Location: USA
|
| Posted: Thu Sep 10, 2009 1:17 pm Post subject: |
|
|
yes VLOOKUP is definitely the way to go
_________________
ʞɔпɟ əɥʇ ʇɐɥʍ |
|
| Back to top |
|
|
sheeblasta
Joined: 09 Sep 2009
Posts: 5
|
| Posted: Thu Sep 10, 2009 4:20 pm Post subject: |
|
|
does Vlookup actually enter data or just check it? i have this,
These are the name columns that need to be compared, but i need some kind of delimiter because sheet 1 names are different bc of -M on the end of their name for Medicaid.
=Sheet1!$A$2:$A$179
='Sheet2'!$D$8:$D$197
which needs to be compared to each other
and =Sheet1!$B$2:$B$179 is the numbers that need to be imported beside each name. |
|
| Back to top |
|
|
sheeblasta
Joined: 09 Sep 2009
Posts: 5
|
| Posted: Thu Sep 10, 2009 6:18 pm Post subject: |
|
|
=Sheet1!$A$2:$A$179
='Sheet2'!$D$8:$D$197
=VLOOKUP(Sheet1!A2:A179, Sheet2!D8:D197,1,FALSE)
This is not working right, I start the formula in d9 sheet 2 and drag it down and it just copys names starting at row 8 in d8 from sheet 1 instead of comparing A2 etc why is this? |
|
| Back to top |
|
|
hutch@edge.net
Joined: 16 Sep 2008
Posts: 77
|
| Posted: Thu Sep 10, 2009 6:36 pm Post subject: |
|
|
If the names on sheet 2 match the names on sheet 1, and the names are in column A of sheet 2, a vlookup would work.
On sheet 1, in cell B2, enter the following formula:
=vlookup(A2,'Sheet2'!$A:$D,4,FALSE)
Now copy that formula all the way down to the end of your data on sheet 1, and it should show the numbers you are looking for. If the matching names are in different columns, you would need to tweak the formula slightly.
Remember, when finished you will want to replace the formulas with the actual values by selecting the range, copying it, and then pasting the values directly over the formulas.
Hope this helps. |
|
| Back to top |
|
|
hutch@edge.net
Joined: 16 Sep 2008
Posts: 77
|
| Posted: Thu Sep 10, 2009 6:38 pm Post subject: |
|
|
| You could post a small sample with some dummy data, that is in the same format, and I will send you back the correct formula. |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
