[TRDood]

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/aspnet/36315892/copying-date-if-date-matches-with-the-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.

[tiger_handheld]

can you not make column 1 = column 2 ?

[Jmac]

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 ...

[TRDood]

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_exchange_rates_help.xlsx

[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. :blush:

Something like this perhaps?
http://www.mediafire.com/?vkcth92dhxtc8ot

[impactX]

Fixed. See tab 3.

http://www.badongo.com/file/25232152

You are welcome. See if you understand what I did.

[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

[Gumby]

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.

[TRDood]

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. :blush:

Something like this perhaps?
http://www.mediafire.com/?vkcth92dhxtc8ot

This is exactly what I am looking for, what did you do there?

[TRDood]

Fixed. See tab 3.

http://www.badongo.com/file/25232152

You are welcome. See if you understand what I did.

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.

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.

Let me try these and I will report back.

[TRDood]

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.

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. :blush:

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. :fullofwin:

[Dasani]

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.

[CP.AR]

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...