Welcome to the E90Fanatics forums. E90Fanatics is the premiere website for the next generation BMW 3 series with interactive forums, photo galleries, and technical information for BMW enthusiasts. If you are looking to talk about your E90 Sedan, E91 Touring, E92 Coupe or E93 Cabrio -- E90Fanatics is the place for you!

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!

If you have any problems with the registration process or your account login, please contact contact us.

Go Back   E90Fanatics > Everything Else > The Off-Topic > General Off-Topic

General Off-Topic
Everything not about BMWs. Posts must be "primetime" safe and in good taste. You must be logged in to see sub-forums.
Click here to browse all new posts.

Reply
 
Thread Tools Search this Thread Rate Thread Display Modes
Old 04-16-2012, 05:34 PM   #1
DrMooCow
Registered User
 
Join Date: Jun 2004
Location: Atlanta
Posts: 298
My Ride: 335d
Send a message via AIM to DrMooCow
Anyone super good at excel?

Have 2 spread sheets. One spread sheet has UPC code of products we're selling. The other spread sheet has the SKU numbers for our inventory. Looking for someone that can merge the UPC # from Excel A into a new column from Excel B.

If you are good at excel and know how to do it, shoot me a pm. Will pay!
DrMooCow is offline   Reply With Quote
Old 04-16-2012, 05:35 PM   #2
Andy2108
Registered User
 
Join Date: Nov 2009
Location: U.S.A.
Posts: 8,039
My Ride: E46 M3, '67 Cobra
Right click... new column

select UPC #'s

Edit > Copy

Go to new column, select first slot

Edit > Paste.


What do I win?
Andy2108 is offline   Reply With Quote
Old 04-16-2012, 05:37 PM   #3
rsxstock
Registered User
 
Join Date: Mar 2009
Location: Canada
Posts: 217
My Ride: 2001 325ci
do both sheets have a common column? just copy that common column with the upc column to sheet B and then filter them and they'll all line up and then delete the extra column
__________________
rsxstock is offline   Reply With Quote
Old 04-16-2012, 05:38 PM   #4
DrMooCow
Registered User
 
Join Date: Jun 2004
Location: Atlanta
Posts: 298
My Ride: 335d
Send a message via AIM to DrMooCow
Excel A has 5497 lines.
Excel B has 3556 lines.

Excel A and B are not sorted at all.
DrMooCow is offline   Reply With Quote
Old 04-16-2012, 05:48 PM   #5
IdahoToyo
Registered User
 
Join Date: May 2009
Location: A Sunny Town in Idaho
Posts: 375
My Ride: 4WD Tundra / GS500F
Are you trying to line up corresponding UPC codes and SKU numbers? From what you have typed, that is what I am getting...you're going to need a common reference value, excel does not know your system and cannot match up UPCs and SKUs on its own.
__________________
-- Kyle --

The difference between school and life? In school, you're taught a lesson and then given a test. In life, you're given a test that teaches you a lesson.
IdahoToyo is online now   Reply With Quote
Old 04-16-2012, 09:10 PM   #6
DrMooCow
Registered User
 
Join Date: Jun 2004
Location: Atlanta
Posts: 298
My Ride: 335d
Send a message via AIM to DrMooCow
Yes, that's exactly what I am trying to do. There's no way for excel to automatically search and add it to each line?
DrMooCow is offline   Reply With Quote
Old 04-16-2012, 09:33 PM   #7
ShootyzGruve
Registered User
 
Join Date: Dec 2011
Location: MA
Posts: 140
My Ride: 2003 325i
Hmmm, thought i just replied but must've made an error.

Anyway OP,

forgive my ignorance but why are the number of elements in each vector (UPC/SKU) different? Are certain values shared for multiple products? Personally Im not that good at excel and don't like using it for analysis. However, if you have access to MATLAB you can 'slave' microsoft office products (excel included) and from here the possibilities are endless. On the other hand for someone that is good at Visual Basic this probably isn't necessary. Im strapped for time right now but may be able to potentially give you some pointers if I understood the data better.
ShootyzGruve is offline   Reply With Quote
Old 04-16-2012, 09:48 PM   #8
JTLaz
Registered User
 
Join Date: May 2005
Location: Tampa, FL
Posts: 325
My Ride: 2010 Wrangler 4 Door
So do you want the sku and upc in the same spreadsheet? matching up to each other as well I'm assuming?

If so do a V-Lookup
JTLaz is offline   Reply With Quote
Old 04-16-2012, 09:59 PM   #9
davidwarren
Halfway to 2L
 
davidwarren's Avatar
 
Join Date: May 2004
Location: louisville, ky
Posts: 2,505
My Ride: 2010 335i
Sounds like you need a database.
__________________

You down with UCC? Ya you know me!
sowing the seeds of jiggery pokery
davidwarren is offline   Reply With Quote
Old 04-16-2012, 10:06 PM   #10
IdahoToyo
Registered User
 
Join Date: May 2009
Location: A Sunny Town in Idaho
Posts: 375
My Ride: 4WD Tundra / GS500F
Quote:
Originally Posted by DrMooCow View Post
Yes, that's exactly what I am trying to do. There's no way for excel to automatically search and add it to each line?
Excel needs to be told which UPC matches to which SKU. To me it sounds as if you have two independent lists, one of UPCs and a second of SKUs, that you are trying to match together. I understand that these two values correlate, however with out a reference sheet of these two (say a third sheet where it has two columns next to each other UPC | SKU) excel does not automatically know your system. If you did have the third sheet you would be able to do a vlookup formula to achieve what you are going for...however I have the feeling if you had this third sheet you wouldn't be in this situation as it is what you are attempting to achieve.

Hopefully I am understanding what you are attempting to accomplish and explaining myself clearly.
__________________
-- Kyle --

The difference between school and life? In school, you're taught a lesson and then given a test. In life, you're given a test that teaches you a lesson.
IdahoToyo is online now   Reply With Quote
Old 04-16-2012, 10:10 PM   #11
dualbob103
Registered User
 
Join Date: Sep 2009
Location: CA
Posts: 313
My Ride: Mums
If your doing spreadsheet to spread sheet, in spread sheet B, where you would to have Spreadsheet A data, put "=(spreadsheet name)!(spot).. example "=Spreadsheet1!D3

at first I thought you wanted to gather data from another spreadsheet without even opening it, but i'll tell you that anyways for anyone who wants to achieve that.

If you wanna pull data from another excel file, into data on a spread sheet, you would "=+'Where file is located]Spreadsheet name'!spot, example "=+'c:/desktop]spreadsheet1'!D3"

Andy2108, What do I win? ; )
dualbob103 is offline   Reply With Quote
Old 04-16-2012, 10:13 PM   #12
dmon_101
Registered User
 
Join Date: Jan 2007
Location: San Diggity, CA
Posts: 5,074
My Ride: has sexy fogs/shoes
Send a message via AIM to dmon_101
import into Access
__________________

Dealer Programmable Options | Projector Fogs FTW
There are a lot of Fs and too many Ks in the F/M/K scenario <- posted before I met my wife
To answer your question, it's something you can find by searching the forum or using Google
dmon_101 is offline   Reply With Quote
Old 04-16-2012, 10:14 PM   #13
ThaHooch
Registered User
 
Join Date: Nov 2006
Location: at home
Posts: 237
My Ride: e46
Quote:
Originally Posted by JTLaz View Post
So do you want the sku and upc in the same spreadsheet? matching up to each other as well I'm assuming?

If so do a V-Lookup
If you have a common column, this is the way to go...
__________________

** Political sig removed **
ThaHooch is offline   Reply With Quote
Old 04-16-2012, 10:41 PM   #14
IdahoToyo
Registered User
 
Join Date: May 2009
Location: A Sunny Town in Idaho
Posts: 375
My Ride: 4WD Tundra / GS500F
Quote:
Originally Posted by ThaHooch View Post
If you have a common column, this is the way to go...
I don't think people are understanding the current state of his data and what he is ultimately trying to achieve

Quote:
Originally Posted by Genius View Post
If your doing spreadsheet to spread sheet, in spread sheet B, where you would to have Spreadsheet A data, put "=(spreadsheet name)!(spot).. example "=Spreadsheet1!D3

at first I thought you wanted to gather data from another spreadsheet without even opening it, but i'll tell you that anyways for anyone who wants to achieve that.

If you wanna pull data from another excel file, into data on a spread sheet, you would "=+'Where file is located]Spreadsheet name'!spot, example "=+'c:/desktop]spreadsheet1'!D3"

Andy2108, What do I win? ; )
Nothing...because that will only put the two columns of data from differing sheets onto one combined sheet. It in no way connects the two lists allowing him to see which UPC lines up to which SKU. What you are telling him to do is just place the two columns next to each others.

Guys he wants to have the UPCs from his first list line up with the corresponding SKUs from the second list, ultimately linking to the two systems -- his his sales side with his production side. He cannot just do a V-lookup. V-Lookup needs something to reference in order to place the corresponding UPCs and SKUs.

If there is no common link between the two number sets as you have them in Excel, there is not much more you can do. It does sound like you need to import the data in a database, Access or SQL, and have someone develop this list you are wanting...with Excel that would be a fun task for an intern to manually match UPCs to SKUs. And importing into a database is still only going to take you so far, you have unique UPC numbers and unique SKU numbers based on what I am reading.
__________________
-- Kyle --

The difference between school and life? In school, you're taught a lesson and then given a test. In life, you're given a test that teaches you a lesson.
IdahoToyo is online now   Reply With Quote
Old 04-16-2012, 10:43 PM   #15
JTLaz
Registered User
 
Join Date: May 2005
Location: Tampa, FL
Posts: 325
My Ride: 2010 Wrangler 4 Door
Quote:
Originally Posted by klubeck View Post
I don't think people are understanding the current state of his data and what he is ultimately trying to achieve



Nothing...because that will only put the two columns of data from differing sheets onto one combined sheet. It in no way connects the two lists allowing him to see which UPC lines up to which SKU. What you are telling him to do is just place the two columns next to each others.

Guys he wants to have the UPCs from his first list line up with the corresponding SKUs from the second list, ultimately linking to the two systems -- his his sales side with his production side. He cannot just do a V-lookup. V-Lookup needs something to reference in order to place the corresponding UPCs and SKUs.

If there is no common link between the two number sets as you have them in Excel, there is not much more you can do. It does sound like you need to import the data in a database, Access or SQL, and have someone develop this list you are wanting...with Excel that would be a fun task for an intern to manually match UPCs to SKUs. And importing into a database is still only going to take you so far, you have unique UPC numbers and unique SKU numbers based on what I am reading.
I said V-Lookup because I assume he has a correlating item with each UPS and SKU. I doubt he would just have a list of SKUs and UPCs in a spreadsheet without any definable characteristics.
JTLaz is offline   Reply With Quote
Old 04-16-2012, 10:49 PM   #16
IdahoToyo
Registered User
 
Join Date: May 2009
Location: A Sunny Town in Idaho
Posts: 375
My Ride: 4WD Tundra / GS500F
Quote:
Originally Posted by JTLaz View Post
I said V-Lookup because I assume he has a correlating item with each UPS and SKU. I doubt he would just have a list of SKUs and UPCs in a spreadsheet without any definable characteristics.
Sorry not saying your method is incorrect by any means, I also agreed with that as an option. However it does almost sound like he has this list....I know it doesn't make any sense and I hope it is to the contrary as it would just be silly for a business to not have a means for tracking that
__________________
-- Kyle --

The difference between school and life? In school, you're taught a lesson and then given a test. In life, you're given a test that teaches you a lesson.
IdahoToyo is online now   Reply With Quote
Old 04-16-2012, 11:11 PM   #17
Andy2108
Registered User
 
Join Date: Nov 2009
Location: U.S.A.
Posts: 8,039
My Ride: E46 M3, '67 Cobra
Quote:
Originally Posted by Genius View Post
If your doing spreadsheet to spread sheet, in spread sheet B, where you would to have Spreadsheet A data, put "=(spreadsheet name)!(spot).. example "=Spreadsheet1!D3

at first I thought you wanted to gather data from another spreadsheet without even opening it, but i'll tell you that anyways for anyone who wants to achieve that.

If you wanna pull data from another excel file, into data on a spread sheet, you would "=+'Where file is located]Spreadsheet name'!spot, example "=+'c:/desktop]spreadsheet1'!D3"

Andy2108, What do I win? ; )
Congratz. I haven't seriously used accel since 6th grade computer class, where we learned Office 2003.
Andy2108 is offline   Reply With Quote
Old 04-17-2012, 01:46 AM   #18
Chris3Duke
Registered User
 
Join Date: Dec 1969
Posts: 222
Just to be clear, your data needs a key. That is, a unique identifier for each record you can use to match the UPC code with the SKU. Without that, you couldn't even do it manually.

Once you have that key variable, it's easy. Access will do it automatically (it's a relational database), or you could do a vlookup with Excel.
Chris3Duke is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On



All times are GMT -5. The time now is 03:08 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.
(c) 1999 - 2011 performanceIX Inc - privacy policy - terms of use