# Solved Sort & Match one column of data with columns of data

Microsoft Excel for mac 2011 - macintosh
August 7, 2012 at 06:01:13
Specs: Macintosh
 Match data in spreadsheet by sorting. Have a spreadsheet I need to import into accounting program. Amazon uses ASIN numbers and everyone else uses ISBN numbers. I have a spreadsheet with all the ASIN and ISBN numbers and book title listed along with all the data listed in columns for each book needed for the accounting program. When I receive a sales data spreadsheet from Amazon I then have four columns of data I need to match with the list of ASIN and ISBN and tile in the accounting spreadsheet. However, not every book sells that month so there would be blanks. When I sort the data doesn't always match up.

See More: Sort & Match one column of data with columns of data

August 7, 2012 at 20:10:55
 AlexandriaLet's see if I can explain this better:=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)ItemToFind is the first ASIN Number in your list on the current sheet, Column F Row 2 which we use to cross reference.RangeToLookIn is the array of cells which contains all the data you will need, in this case from: Amazon Sheet Column B Row 2 (which is the ASIN cross reference) to Amazon Sheet Column J Row 4ColumnToPickFrom is dependent on the data you wish to extract from the above array. If you want the Price, then starting at Column B on the Amazon Sheet, which is the 1st column in the array, you count the number of column to the Price column, which (I think) is column G, which is 6 columns to the right.SortedOrUnsorted The Sorted/Unsorted is whether the column headings are sorted. 1 = TRUE for Yes, 0 = FALSE for No, just use 0 = False.So your new =VLOOKUP() will look something like:=VLOOKUP(F2,Amazon!B2:J4,6,0)The \$ signs are used to anchor a cell so when the formula is copied the cell reference does not move.Now, since we always want the ASIN column, we use \$F2, which anchors ONLY column F not the row number. This way we can now copy it down.And since we need to search ALL of the array on the Amazon sheet, we anchors both Columns and Rows, like \$B\$2:\$J\$4 so nothing will move whenwe copy the formula.So the formula to get the Price now looks like:=VLOOKUP(\$F2,Amazon!\$B\$2:\$J\$4,6,0)If your Sheet Name has a space in it, like Amazon UK, you will need tosurround the name with single quotes, like:=VLOOKUP(\$F2,'Amazon UK'!\$B\$2:\$J\$4,6,0)Does that help?MIKEhttp://www.skeptic.com/

#1
August 7, 2012 at 07:25:55
 Without knowing how your data is setup, I would suggest youresearch the =VLOOKUP() command.Since you key on the ASIN and/or ISBN you can use that as your cross-reference point.MIKEhttp://www.skeptic.com/

Report •

#2
August 7, 2012 at 07:34:36
 Mike,Thank you so much for taking the time to answer.I have columns of data with sales information for the titles sold. Title only has ASIN number, not the ISBN. Accounting software only uses ISBN. I have a spreadsheet will all 200 titles and all the banal information needed in columns for the accounting software. I need to match up the title in the sales columns with the titles in the accounting spreadsheet. Some titles don't sell so if I do a normal sort it will not leave blanks on the ones without sales, etc. Does this help?Alexandria

Report •

#3
August 7, 2012 at 10:08:46
 Which fields in the Amazon list matches which fields in Your spreadsheet?It would help if you post a small sample of your spreadsheets with column letters and row numbers and a few lines of sample data from both sheets, BUT first read this How-To on how to post your data in this forum:http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
August 7, 2012 at 11:12:42
 Thank you again so much for your help.This is just the important part of the spreadsheet. There are many more columns of data, but this section is the only one which needs to be changed.```Item Number ASIN Description Quantity Price Total Job 9781603101448 B001B4E5YG A Christmas Cara AChristmas Cara 9781603101745 B001B48ZTM A Damsel In Distress A Damsel in Des 9781603104289 B0038BRTB6 A Maiden Of Mercy A MaidenofMercy 9781603106986 B005RR1S6Q A Tavern Wench To Bed A Tavern Wench 9781603105590 B004C44PD8 A Wicked Wolf A Wicked Wolf```When I upload the file I don't select ASIN as it is not in the program.Then the sales report only has the ASIN```ASIN Description Quantity Price Total B001B4E5YG A Christmas Cara B001B48ZTM A Damsel In Distress 1 1.75 2.52 B0038BRTB6 A Maiden Of Mercy B005RR1S6Q A Tavern Wench To Bed B004C44PD8 A Wicked Wolf 1 1.75 2.52```Does this help?

Report •

#5
August 7, 2012 at 12:00:36
 Since you did not include column letters or row numbers, I'm guessing your datalook like this:Your Sheet``` A B C D E F G 1) Item Number ASIN Description Quantity Price Total Job 2) 9781603101448 B001B4E5YG A Christmas Cara AChristmas Cara 3) 9781603101745 B001B48ZTM A Damsel In Distress A Damsel in Des 4) 9781603104289 B0038BRTB6 A Maiden Of Mercy A MaidenofMercy 5) 9781603106986 B005RR1S6Q A Tavern Wench To Bed A Tavern Wench 6) 9781603105590 B004C44PD8 A Wicked Wolf A Wicked Wolf ```Amazon Sheet:``` A B C D E 1) ASIN Description Quantity Price Total 2) B001B4E5YG A Christmas Cara 3) B001B48ZTM A Damsel In Distress 1 1.75 2.52 4) B0038BRTB6 A Maiden Of Mercy 5) B005RR1S6Q A Tavern Wench To Bed 6) B004C44PD8 A Wicked Wolf 1 1.75 2.52 ```and you want Your Sheet to look like this:``` A B C D E F G 1) Item Number ASIN Description Quantity Price Total Job 2) 9781603101448 B001B4E5YG A Christmas Cara 0 0 0 AChristmas Cara 3) 9781603101745 B001B48ZTM A Damsel In Distress 1 1.75 2.52 A Damsel in Des 4) 9781603104289 B0038BRTB6 A Maiden Of Mercy 0 0 0 A MaidenofMercy 5) 9781603106986 B005RR1S6Q A Tavern Wench To Bed 0 0 0 A Tavern Wench 6) 9781603105590 B004C44PD8 A Wicked Wolf 1 1.75 2.52 A Wicked Wolf ```In cell D2 enter the formula: =VLOOKUP(B2,Amazon!\$A\$2:\$D\$6,3,0)Drag downin cell E2 enter the formula: =VLOOKUP(B2,Amazon!\$A\$2:\$D\$6,4,0)Drag downin cell F2 enter the formula: =VLOOKUP(B2,Amazon!\$A\$2:\$D\$6,5,0)Drag downYou will notice that all three formula arealmost identical, with only one number different.A =VLOOKUP() is defined as:=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)The difference is which ColumnToPickFromchanges with the column where in, IE Quantity or Price or Total.Change the word Amazon in the formula to match what ever is the name of the sheet you receive.See how that works.MIKE

Report •

#6
August 7, 2012 at 12:40:59
 Mike,First you are brilliant for understanding what I need when I didn't send the ABC or the 123 of the list.So I need to have the data on two different spreadsheets and not try to sort. But to put the formula in the column. OK, I know I am very inexperienced in doing this and I sound really dumb, but I have never written a formula for a spreadsheet.When I click on the cell and select insert a function it says Look UP and has two options. Look UP - Value or look up array. I would assume Value?Also, Amazon is a reference to the name of the spreadsheet? Is the the name on the bottom tab of the workbook?Thank you! Thank you! Thank you! so much for all this help!Alexandria

Report •

#7
August 7, 2012 at 13:08:50
 Mike,```(B2, Amazon! \$A\$2:\$D\$6, 3,0) (ItemToFind, RangeToLookIn, ColumnToPickFrom, SortedOrUnsorted)```Is this the formula? I don't understand the formula. Do you write drag down?Do I have it broken down correctly above?D2 cell (B2,Amazon!\$A\$2:\$D\$6,3,0)E2 cell (B2,Amazon!\$A\$2:\$D\$6,4,0)F2 cell (B2,Amazon!\$A\$2:\$D\$6,5,0)Yes, I do have a high IQ but I need to be perfect so it takes me a while sometimes. Sorry to be so dense.Alexandria

Report •

#8
August 7, 2012 at 13:14:37
 I would assume Value?Yesor you could just Copy & Paste from my post.Copy the formula from aboveand then select the cell and then Paste the formula.Amazon is a reference to the name of the spreadsheet? Yes and No,In Excel we call a File a Workbook, and it usually consists of three Sheets.In the above formula, Amazon, is a reference tothe Sheet named Amazon, in the current Workbook,.Is the Amazon data in the same Workbook as Your data or is it in it's own separate (File) Workbook?If it is in it's own separate Workbook,, you will need to include the Workbook name,as well as the Sheet name.If you receive the Workbook (FILE) and it is named "AmazonList.xlsx"and the data your after is on a SHEET named "LIST"then you would change the formula to look something like:=VLOOKUP(B2,[Amazon.xlsx]List!\$A\$2:\$E\$6,5,0)"Amazon.xlsx" is the Workbook / File name"List" is the Sheet NameThe square brackets and exclamation mark are required.MIKEhttp://www.skeptic.com/

Report •

#9
August 7, 2012 at 19:20:36
 Mike,Had to go to a doctor’s appointment and then take some calls. Sorry to get back so long after you wrote.Then I tried to write the formulas. OK, I guess I am not as smart as I think I am. I sent you an example thinking I could just modify the formula for the real spread sheet. I am good with formulas, but I don’t know what all your symbols mean to change it. So I am sending you the real spreadsheet with the somewhat real amount of A, B, C on the top (actually it goes to BC, but I don't think they are relevant) and the length is about 200 rows can be 400 soon. Do you need an exact count on the rows? The rows will change over time.I am going to have one workbook for each Amazon account. One sheet is MYOB for the accounting software and Amazon UK for the Amazon Spreadsheet. They are in one Excel workbook. The next workbook sheets will be MYOB and Amazon US, etc. for the other workbooks.The rows and columns will be the same in all of the workbooks for MYOB until I add new product. The rows will change every time there is a new sales report with Amazon.Can you please tell me what the formula would be for the two spreadsheets below. Please I know I have been a pain all day, but you would save my life if you can help me with this. MYOB Spreadsheet``` A B C D E F G H I J K L M N O Co./Last Name First Name Invoice # Date Customer PO Ship Via Delivery Item Number ASIN Job Description Quantity Price Discount Total 1) AMAZON DIGITAL - UK AMAZON DIGITAL - UK 1205AMUK 7/31/12 2012/05 Amazon UK P 9781603101448 B001B4E5YG A Christmas Car A Christmas Cara 2) AMAZON DIGITAL - UK AMAZON DIGITAL - UK 1205AMUK 7/31/12 2012/05 Amazon UK P 9781603101745 B001B48ZTM A Damsel in Des A Damsel In Distress 3) AMAZON DIGITAL - UK AMAZON DIGITAL - UK 1205AMUK 7/31/12 2012/05 Amazon UK P 9781603104289 B0038BRTB6 A Maiden of Mer A Maiden Of Mercy 4) AMAZON DIGITAL - UK AMAZON DIGITAL - UK 1205AMUK 7/31/12 2012/05 Amazon UK P 9781603106986 B005RR1S6Q A Tavern Wench A Tavern Wench to Bed 5) AMAZON DIGITAL - UK AMAZON DIGITAL - UK 1205AMUK 7/31/12 2012/05 Amazon UK P 9781603105590 B004C44PD8 A Wicked Wolf A Wicked Wolf ```Amazon```A B C D E F G H I J K L Title ASIN Transaction Type* Units Sold Units Refunded Net Units Sold Percentage of Borrows*** Average List Price Average Offer Price Average File Size Average Delivery Cost Royalty 1) The Doctor Next Door B001B4AVTE 0.7 2 0 2 N/A 2.36 2.36 0.12 0.01 3.28 2) Master of the Elements B001B4G846 0.7 2 0 2 N/A 1.88 1.88 0.09 0.01 2.62 3) FORBIDDEN FRUIT B001B4G89Q 0.7 15 2 13 N/A 2.51 1.93 0.15 0.01 17.42```I know the products don't match this time. I only used the top rows for an example. BTW I went to your site or the site you like called http://www.skeptic.com/ What a great site! I am coming back to read about the twin study. I have followed it for years!Thank you ! Thank you! Thank you!!!Alexandria

Report •

#10
August 7, 2012 at 20:10:55