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

Report •


✔ Best Answer
August 7, 2012 at 20:10:55
Alexandria

Let'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 4

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

http://www.skeptic.com/



#1
August 7, 2012 at 07:25:55
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

http://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...

MIKE

http://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 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 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 down

in cell E2 enter the formula: =VLOOKUP(B2,Amazon!$A$2:$D$6,4,0)
Drag down

in cell F2 enter the formula: =VLOOKUP(B2,Amazon!$A$2:$D$6,5,0)
Drag down

You 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

http://www.skeptic.com/


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?

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
the 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 Name

The square brackets and exclamation mark are required.

MIKE

http://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
✔ Best Answer
Alexandria

Let'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 4

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

http://www.skeptic.com/


Report •

#11
August 8, 2012 at 04:47:40
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 B

Your 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 B

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

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


Report •


Ask Question