Anyone super good at excel?

[DrMooCow]

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!

[Andy2108]

Right click... new column

select UPC #'s

Edit > Copy

Go to new column, select first slot

Edit > Paste.


What do I win?

[rsxstock]

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

[DrMooCow]

Excel A has 5497 lines.
Excel B has 3556 lines.

Excel A and B are not sorted at all.

[IdahoToyo]

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.

[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?

[ShootyzGruve]

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.

[JTLaz]

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

[davidwarren]

Sounds like you need a database.

[IdahoToyo]

Originally Posted by 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?

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.

[Genius]

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? ; )

[dmon_101]

import into Access

[ThaHooch]

Originally Posted by JTLaz

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

[IdahoToyo]

Originally Posted by ThaHooch

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

Originally Posted by Genius

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.

[JTLaz]

Originally Posted by klubeck

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.

[IdahoToyo]

Originally Posted by JTLaz

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

[Andy2108]

Originally Posted by Genius

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.

[Chris3Duke]

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.