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.

✔ Best Answer

Alexandria Let's see if I can explain this better:

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

ItemToFindis the first ASIN Number in your list on the current sheet,

Column F Row 2 which we use to cross reference.

RangeToLookInis 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 4

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

SortedOrUnsortedThe 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

anchora 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 when

we 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 to

surround the name with single quotes, like:=VLOOKUP($F2,'Amazon UK'!$B$2:$J$4,6,0)

Does that help?

MIKE

Without knowing how your data is setup, I would suggest you

research the =VLOOKUP() command.Since you key on the ASIN and/or ISBN you can use that as your

cross-reference point.MIKE

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

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,

BUTfirst read thisHow-Toon how to post your data in this forum:http://www.computing.net/howtos/sho...

MIKE

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 WolfWhen 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.52Does this help?

Since you did not include column letters or row numbers, I'm guessing your data

look 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 WolfAmazon 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.52and 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 WolfIn 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 are

almost identical, with only one number different.A =VLOOKUP() is defined as:

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

The difference is which

ColumnToPickFrom

changes 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

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

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

I would assume Value?Yes

or you could just Copy & Paste from my post.Copy the formula from above

and 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 to

theSheetnamed Amazon, in the currentWorkbook,.

Is the Amazon data in the sameWorkbookas 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 theWorkbookname,

as well as theSheetname.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 / Filename"List" is the

SheetNameThe square brackets and exclamation mark are required.

MIKE

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

AmazonA 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

Alexandria Let's see if I can explain this better:

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

ItemToFindis the first ASIN Number in your list on the current sheet,

Column F Row 2 which we use to cross reference.

RangeToLookInis 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 4

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

SortedOrUnsortedThe 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

anchora 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 when

we 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 to

surround the name with single quotes, like:=VLOOKUP($F2,'Amazon UK'!$B$2:$J$4,6,0)

Does that help?

MIKE

Mike, Let's see if I can explain this better: YES, you did! THANK YOU SO VERY MUCH!

1. ItemToFind is the first ASIN Number in your list on the MYOB sheet, Column F Row 2 which we use to cross reference.The spreadsheet column row headers are different for the ASIN number on the MYOB sheet I have is Column H Row 2

Wish I could have attached a spreadsheet for you.=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

Your example =VLOOKUP($F2,Amazon!$B$2:$J$4,6,0)

My formula =VLOOKUP($H2,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

MYOB ASIN Column H Row 2

2. 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 4=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

Your example =VLOOKUP($F2,Amazon!$B$2:$J$4,6,0)

The AmazonUK spreadsheet columns go from A to L with 54 rows of data. ASIN is in Column B. Price is in the H Column with 54 rows of data.

My formula =VLOOKUP($H2, AmazonUK!$B$2:$L$54,SortedOrUnsorted)

MYOB ASIN Column H Row 2

AmazonUK ASIN Column B Row 2 to Column L and is 54 rows

3. ColumnToPickFrom is dependent on the data you wish to extract from the above array. 1) If you want the Price

1) 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.GOD YOU ARE SO SMART. Reading that stupid spreadsheet is an act in futility unless you are brilliant.

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

1) If you want the Price, then starting at Column B (ASIN) or (=VLOOKUP($H2)) on the Amazon Sheet,

which is the 1st column in the array (=VLOOKUP($H2)) you count the number of column to the Price column,

which is column H, which is 6 columns to the right of Column BYour example =VLOOKUP($F2,Amazon!$B$2:$J$4,6,0)

My formula =VLOOKUP($H2, AmazonUK!$B$2:$L$54,6,SortedOrUnsorted)

MYOB ASIN Column H Row 2

AmazonUK ASIN Column B Row 2 and is 54 rows

AmazonUK ASIN Column B Row 2 to Column L and is 54 rows and PRICE is 6 rows over from column B

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

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

Your example =VLOOKUP($F2,Amazon!$B$2:$J$4,6,0)

My formula =VLOOKUP($H2, AmazonUK!$B$2:$L$54,6,0)

MYOB ASIN Column H Row 2

AmazonUK ASIN Column B Row 2 and is 54 rows

AmazonUK ASIN Column B Row 2 to Column L and is 54 rows and PRICE is 6 rows over from column B & Unsorted 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 when we copy the formula.

So the formula to get the Price now looks like: =VLOOKUP($F2,Amazon!$B$2:$J$4,6,0)

Your example =VLOOKUP($F2,Amazon!$B$2:$J$4,6,0)

My formula =VLOOKUP($H2, AmazonUK!$B$2:$L$54,6,0)If your Sheet Name has a space in it, like Amazon UK, you will need to surround the name with single quotes, like: =VLOOKUP($F2,'Amazon UK'!$B$2:$J$4,6,0)

Changed the sheet name to AmazonUK so this won’t be a problem.

From your previous post this would now be:1. Price In cell E2 enter the formula: =VLOOKUP(B2,Amazon!$A$2:$D$6,4,0) Drag down

My formula =VLOOKUP($H2,AmazonUK!$B$2:$L$54,6,0)Drag down

MYOB ASIN Column H Row 2

AmazonUK Average List Price Column H which is 6 rows over from AmazonUK ASIN Column B2. Quantity In cell D2 enter the formula: =VLOOKUP(B2,Amazon!$A$2:$D$6,3,0)Drag down

My formula =VLOOKUP($H2,AmazonUK!$B$2:$L$54,4,0)Drag down

MYOB ASIN Column H Row 2

AmazonUK Column F Net Units Sold or Borrowed which is 4 rows over from AmazonUK ASIN Column B3. Total In cell F2 enter the formula: =VLOOKUP(B2,Amazon!$A$2:$D$6,5,0) Drag down

My formula =VLOOKUP($H2,AmazonUK!$B$2:$L$54,10,0)Drag down

MYOB ASIN Column H Row 2

AmazonUK Column L Royalty which is 10 rows over from AmazonUK ASIN Column B

By Jo I think I got it! ☺Again, thank you for your patients and kindness and explaining so beautifully!!

Of course let me know if you see any glaring mistakes. ☺ Off to go try it.

Alexandria

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History