Office & Productivity Software

Resolved Question

I have two data sheet in excel, how to join them together?

by AngrybirdII / January 21, 2013 4:08 PM PST

hi, all,

Plz help me! I have two customer data sheet in excel.The first sheet is mainly customer info and consumption detail in 2011, the second is the customer sheet for consumption in 2012. Each customer with a unique Customer ID. I want to pick out the old customer of 2011 from 2012, and analysis their detail consumption info, and decide a best customized marketing plan for them. So how can i do this quickly, since i have more than 10 thousand customers.

AngrybirdII has chosen the best answer to their question. View answer »
Answer This Ask For Clarification
Discussion is locked
You are posting a reply to: I have two data sheet in excel, how to join them together?
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: I have two data sheet in excel, how to join them together?
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Clarification Request
It's not clear the 'extent of joining' you
by VAPCMD / January 22, 2013 9:17 AM PST

really want or need to do.

Do you want to just put all the data in the same workbook ?

Do you want to put part (a couple of fields) of the data from 2011 worksheet into 2012 worksheet ?

Depending on how the data is laid out, pulling selected data entries from one spreadsheet to another is fairly simple.

Lots of possiblities...just not sure how much data you want bring over and how it will be laid out.

VAPCMD

Collapse -
i want to pick out the old customers in 2011 from 2012
by AngrybirdII / January 23, 2013 2:14 PM PST

hi,
I want to put part of the data from 2011 worksheet into 2012 worksheet, but also want to pick out the old customers of 2011 from 2012 sheet, and join their consumption details in one sheet. So i can know the old customers consumption trends in the last two years. There are columns like Customer ID (unique number for every customer), Date, Products, Costs.

So can you help me?

Collapse -
That's easy with a VLOOKUP-formula.
by Kees_B Forum moderator / January 23, 2013 3:54 PM PST

Adding the 2011-only customers to the table-of-all-customers is much more difficult.

Kees

Collapse -
Did you try VLOOKUP ? Pretty easy and lots of
by VAPCMD / January 28, 2013 11:38 AM PST

online tutorials. Simle enough to try.

VAPCMD

All Answers

Best Answer as chosen by AngrybirdII

Collapse -
Try this method
by KelvinClark / January 24, 2013 2:36 PM PST

First, join data.

Import your two spreadsheets into the application. File - Open file in other format - excel with header.
Then, right click on a detail cell below ID - Cell - Set/Cancel master cell. set master cell to the other sheet.
Copy a single detail row in 2012, move mouse to the sheet 2011, append enough columns to put your records. Then, right click the cell - Operations - Join, choose left join.

You can find the records are joined by your customer ID, and only left the common customers.

Second, remove the column or rows you don't need.

Download the application from http://www.raqsoft.com/download.html.

Collapse -
almost forget, it's name is esCalc
by KelvinClark / January 24, 2013 2:48 PM PST
In reply to: Try this method

you should download esCalc.

Collapse -
Thanks for your help!
by AngrybirdII / January 28, 2013 10:47 AM PST

Thanks for your help, i followed u, and get what i want, but i tried twice to get what i want, because i get to use it for the first time~

Collapse -
Answer
Re: joining
by Kees_B Forum moderator / January 21, 2013 4:13 PM PST

In Excel, the usual command is VLOOKUP.
If you import the sheets in MS Access, it's an INNER JOIN in SQL.

In both cases, you'll probably have to write some VBA code to do the analysis and propose a customised marketing plan. If you have to do that manually, and it's 1 minute per customer, it's 10.000 minutes (133 hours = 3+ weeks), which might be not quick enough.

Kees

Collapse -
I don't know much about writing a SQL or VBA code...
by datakeyword / January 21, 2013 4:49 PM PST
In reply to: Re: joining

Hi, kees,

Thanks for your help. Well. it does take much time to do it manually. I will try the MS access, it seems the access is much easier then write codes in VBA...If any problem, do you mind helping me?

Collapse -
Re: help
by Kees_B Forum moderator / January 21, 2013 4:57 PM PST

Personally, I fiind Access VBA somewhat easier than Excel VBA, but it's more difficult then Excel formulas. So it really depends on the details of the problem- and the experience of the programmer - what's the best choice.

Since it's for business, better hire a programmer if it takes too much time or too much help to do it yourself. It's tax deductable and good for the economy moreover.

Kees

Popular Forums
icon
Computer Help 47,885 discussions
icon
Computer Newbies 10,322 discussions
icon
iPhones, iPods, & iPads 3,188 discussions
icon
Security 30,333 discussions
icon
TVs & Home Theaters 20,177 discussions
icon
HDTV Picture Setting 1,932 discussions
icon
Phones 15,713 discussions
icon
Windows 7 6,210 discussions
icon
Networking & Wireless 14,510 discussions

Tech for the holiday

Find recipes for July 4 with these foodie apps

The Fourth of July means fireworks, fun and food. If you're planning on a barbecue this weekend, we've got the apps to help you find holiday-inspired recipes.