Computer Tech, Gaming & Electronics THIS SPACE OPEN FOR ADVERTISEMENT. YOU SHOULD BE ADVERTISING HERE!
Silicon Valley.
Tips & tricks, tech support, home theatre, online gaming, reviews, latest news... |
 | 
03-21-2011, 07:20 PM
|
#1 | | I answer every Emotion with an emoticon
Join Date: Jul 2006 Location: V
Posts: 7,849
Thanked 656 Times in 326 Posts
| Excel Gurus please help
I am trying to match 2 columns together, basically I want to merge 2 data sets together but they have different dates.
e.g.
1/22/2007 6.633 Jan 22, 2007 51.11
1/23/2007 6.6193 Jan 23, 2007 53.61
1/24/2007 6.5801 Jan 24, 2007 54.24
1/25/2007 6.582 Jan 25, 2007 53.49
1/26/2007 6.593 Jan 26, 2007 55.38 1/27/2007 6.5789 Jan 29, 2007 54.01 1/28/2007 6.5916 Jan 30, 2007 57.03
1/29/2007 6.5918 Jan 31, 2007 58.17
1/30/2007 6.5824 Feb 01, 2007 57.35
1/31/2007 6.577 Feb 02, 2007 59.01
2/1/2007 6.587 Feb 05, 2007 58.69
I have to delete the bolded ones so that the 2 columns match, similar to this... http://www.eggheadcafe.com/software/...reference.aspx
Thanks!
Looking for simple excel solutions, not VBA... I have about 1200 data points, it will take me hours to delete all the weekends.
| 
|  | |
03-21-2011, 08:22 PM
|
#2 | | MiX iT Up!
Join Date: May 2006 Location: vancouver
Posts: 8,133
Thanked 2,066 Times in 865 Posts
|
can you not make column 1 = column 2 ?
__________________
Sometimes we tend to be in despair when the person we love leaves us, but the truth is, it's not our loss, but theirs, for they left the only person who couldn't give up on them.
Make the effort and take the risk..
"Do what you feel in your heart to be right- for you'll be criticized anyway. You'll be damned if you do, and damned if you don't." - Eleanor Roosevelt | |
| | |
03-21-2011, 08:28 PM
|
#3 | | Willing to sell body for a few minutes on RS
Join Date: Aug 2004 Location: Duncan, BC
Posts: 10,127
Thanked 5,568 Times in 2,107 Posts
|
Why not just make column A = column C ???
For column A,
=C:C
I assume column C has the actual date and column A is simply an A?+1 formula, since you say you want to delete all the weekends.
Need more info for correct results ...
Last edited by Jmac; 03-21-2011 at 08:36 PM.
| |
| | |
03-21-2011, 09:37 PM
|
#4 | | I answer every Emotion with an emoticon
Join Date: Jul 2006 Location: V
Posts: 7,849
Thanked 656 Times in 326 Posts
|
hmmm, not sure what you guys mean.
basically I need to make sure that the dates match for exchange rates and crude oil prices.
I have done up to April 2007. For example, in column A, April 21 and 22 have to be deleted because column D jumps from April 20 to April 23. (Crude oil markets don't open on weekends)
The results should be that all dates match up until Mar 15, 2011.
download file here: http://www.sfu.ca/~lcheung/cny_jpy_e...ates_help.xlsx | |
| | |
03-22-2011, 01:23 AM
|
#5 | | I am Hook'd on RS
Join Date: Aug 2005 Location: Unknown
Posts: 56
Thanked 13 Times in 9 Posts
|
Hmm, so basically column A to have the same dates as column D? I don't really know anything about excel, but it sounds like the general idea. 
Something like this perhaps? http://www.mediafire.com/?vkcth92dhxtc8ot | |
| | |
03-22-2011, 05:34 AM
|
#6 | | The "You'd Know" Moderator
Join Date: Apr 2001 Location: Home
Posts: 20,930
Thanked 274 Times in 139 Posts
|
Fixed. See tab 3. http://www.badongo.com/file/25232152
You are welcome. See if you understand what I did.
__________________
08 CBR600RR
03 IS300
Ezekiel 25:17. The path of the righteous man is beset on all sides by the inequities of the selfish and the tyranny of evil men. Blessed is he who, in the name of charity and good will, shepherds the weak through the valley of the darkness. For he is truly his brother's keeper and the finder of lost children. And I will strike down upon thee with great vengeance and furious anger those who attempt to poison and destroy my brothers. And you will know I am the Lord when I lay my vengeance upon you. | |
| | |
03-22-2011, 08:13 AM
|
#7 | | RS controls my life!
Join Date: Jan 2002
Posts: 713
Thanked 42 Times in 32 Posts
|
Simple solution: Vlookup
Cells:
a1) 1/22/2007
b1) 6.633
c1) Jan 22, 2007
d1) 51.11
Step1)In a new column (column e) lookup using the following formula:
=VLOOKUP(A1,C: D,2,false)
run this formula for all your 1200 rows
Step 2) Select all cells and sort by column e
/Done. takes 30 seconds
| |
| | |
03-22-2011, 09:04 AM
|
#8 | | Willing to sell body for a few minutes on RS
Join Date: Aug 2002 Location: Vancouver
Posts: 10,064
Thanked 3,080 Times in 1,187 Posts
| Quote:
Originally Posted by Wetordry  Simple solution: Vlookup
Cells:
a1) 1/22/2007
b1) 6.633
c1) Jan 22, 2007
d1) 51.11
Step1)In a new column (column e) lookup using the following formula:
=VLOOKUP(A1,C: D,2,false)
run this formula for all your 1200 rows
Step 2) Select all cells and sort by column e
/Done. takes 30 seconds | Given =VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup]), I think you have the lookup_value and table_array flipped.
It should be =VLOOKUP(C1, A:B,2,"false").
The way you have it, you won't find the two bolded dates (from column A) in column C.
__________________
Do Not Put Aftershave on Your Balls. -604CEFIRO
Looks like I'm gonna have some hot sex again tonight...OOPS i got the 6 pack. that wont last me the night, I better go back and get the 24 pack! -Turbo E
kinda off topic but obama is a dilf - miss_crayon
Honest to fucking Christ the easiest way to get a married woman in the mood is clean the house and do the laundry.....I've been with the same girl almost 17 years, ask me how I know. - quasi
| 
| | |
03-22-2011, 08:05 PM
|
#9 | | I answer every Emotion with an emoticon
Join Date: Jul 2006 Location: V
Posts: 7,849
Thanked 656 Times in 326 Posts
| Quote:
Originally Posted by Dasani Hmm, so basically column A to have the same dates as column D? I don't really know anything about excel, but it sounds like the general idea.
Something like this perhaps? http://www.mediafire.com/?vkcth92dhxtc8ot | This is exactly what I am looking for, what did you do there?
| |
| | |
03-22-2011, 08:07 PM
|
#10 | | I answer every Emotion with an emoticon
Join Date: Jul 2006 Location: V
Posts: 7,849
Thanked 656 Times in 326 Posts
| Quote:
Originally Posted by impactX | I don't see any formulas, where can I find out what you did?
not really what I want, but it helps identifying what's not needed. Quote:
Originally Posted by Wetordry Simple solution: Vlookup
Cells:
a1) 1/22/2007
b1) 6.633
c1) Jan 22, 2007
d1) 51.11
Step1)In a new column (column e) lookup using the following formula:
=VLOOKUP(A1,C: D,2,false)
run this formula for all your 1200 rows
Step 2) Select all cells and sort by column e
/Done. takes 30 seconds | Quote:
Originally Posted by Gumby Given =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), I think you have the lookup_value and table_array flipped.
It should be =VLOOKUP(C1, A:B,2,"false").
The way you have it, you won't find the two bolded dates (from column A) in column C. | Let me try these and I will report back.
| |
| | |
03-22-2011, 08:40 PM
|
#11 | | I answer every Emotion with an emoticon
Join Date: Jul 2006 Location: V
Posts: 7,849
Thanked 656 Times in 326 Posts
| Quote:
Originally Posted by Gumby Given =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), I think you have the lookup_value and table_array flipped.
It should be =VLOOKUP(C1, A:B,2,"false").
The way you have it, you won't find the two bolded dates (from column A) in column C. | Quote:
Originally Posted by Dasani Hmm, so basically column A to have the same dates as column D? I don't really know anything about excel, but it sounds like the general idea.
Something like this perhaps? http://www.mediafire.com/?vkcth92dhxtc8ot | DING DING DING
I figured it out!!!! Thanks Gumby for the formula, exactly what I needed. (Also to produce Dasani's results)
And thanks to all of you who helped.  | |
| | |
03-22-2011, 09:06 PM
|
#12 | | I am Hook'd on RS
Join Date: Aug 2005 Location: Unknown
Posts: 56
Thanked 13 Times in 9 Posts
| Quote:
Originally Posted by TRDood This is exactly what I am looking for, what did you do there? | Sorry, I should have posted what I had done, but I'm glad it's solved.
I just copied the dates from column D over to column A then used paste special (values) and it converted the date format.
| |
| | |
02-09-2012, 09:58 PM
|
#13 | | YOU CANT CUT BACK ON FUNDING! YOU WILL REGRET THIS
Join Date: Feb 2009 Location: FL400
Posts: 5,865
Thanked 3,092 Times in 1,038 Posts
|
BUMP!!!!
This is more VBA/Macro Related, so if you can please help please chime in.
Basically I'm trying to create a form that will cut some of the repetitive work when entering addresses for clients and whatnot.
I don't know anything for macros beside recording them like a :noob:.
Anyways, I want to make a drop-down list of names I can click on, and automatically have a set of fields filled out.
ie: if I select "CUSTOMER A" From the list, it will run a macro that will fill all the address, and telephone number fields out as well...
__________________
Where the hell am I
| |
| | |
Posting Rules
| You may not post new threads You may not post replies You may not post attachments You may not edit your posts
HTML code is Off
| | |
All times are GMT -8. The time now is 03:20 PM.
|
You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join our community today!
The banners on the left side and below do not show for registered users!
