Getting All Values

cybermatrix

New Member
Joined
Jun 2, 2010
Messages
37
How can I get all the values from one worksheet to another one if it matches the value I'm looking for? Vlookup only provides the first time it finds it. I have looked at index and match functions. Seems to me it needs to combine vlookup, index and match to make this work. However, that I'm finding is I have to provide that given value to get all the values. I have also thought about a pivot table.

This is what I need. I have a WS that has a list of sku's with the date in the next column. The other WS has a list of the each sku and how many times it has been ordered. I need to know each date that sku has been ordered.

WS with all the sku's and the date it was ordered, over 20,000 rows

SkuOrder Date
8036726/23/2014
2606446/23/2014
5246016/23/2014
B101070046/23/2014
B61350BY46/23/2014
4032856/23/2014
B61350BY46/23/2014
B61350BY46/23/2014
6459246/23/2014
6335956/23/2014

<tbody>
</tbody>


WS with how many times it was ordered and the Dates Ordered column needs to have each date it was ordered. Or it can add a column to be Ordered Date2, etc.

SKUQty OrderedDates Ordered
B0891000316
B4110700615
B61350BY413
B3221047712
44768510
7044958
B064075358

<tbody>
</tbody>


This might not be the best way to go about it, but it will give a good idea of the information I need to get.

Any suggestions and help would be appreciated.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
Set a pivot table and drag the desired field into report filter so each group will have its own page. Or filter in place by checking just the boxes of the values to be kept. Choose the count calculation if it isn't automatic. There are lookup formulas that can return multiple instances of a value too.
 
Last edited:

cybermatrix

New Member
Joined
Jun 2, 2010
Messages
37
I have set filter as stated. However there are about 1000 skus to check. Unless I'm missing something I would need to select each sku about 1000 times. Or am I'm missing something
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
Actually you just need the advanced filter, no pivot table, to do this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Sku</td><td style=";">Order Date</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">803672</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">260644</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">524601</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">B10107004</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">B61350BY4</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">403285</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">B61350BY4</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">B61350BY4</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">645924</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">633595</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Sku</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">B61350BY4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">403285</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">645924</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Data---Advanced (next to filter)----Filter the List in place --List range: A1:B11 ----Criteria Range: A13:B16

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Sku</td><td style=";">Order Date</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">B61350BY4</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">403285</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">B61350BY4</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">B61350BY4</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">645924</td><td style="text-align: right;;">6/23/2014</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Sku</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">B61350BY4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">403285</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">645924</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,486
Office Version
  1. 2013
Platform
  1. Windows
If I understand correctly:
Item B08910003 Has been ordered 16 different times and you want each of those dates show like this: Item Number, Number times, Date, Date, Date. Item number would be in A1 Number times ordered in B1 Dates in C1, D1,E1 etc. Is this correct?
 
Last edited:

cybermatrix

New Member
Joined
Jun 2, 2010
Messages
37
It does not matter to me if all the dates are in a row or columns. Prefer rows so it is easier to work. The main thing I'm looking for is to be able to have a sheet that only shows the sku's ordered with the date each time it was ordered. The 1000 sku's I'm matching up are those that have been returned to the warehouse. The list of all sku's sold has over 20K different sku's. So I'm trying to find those returned sku's from the ordered list with the dates they were ordered.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,203
Messages
5,629,265
Members
416,382
Latest member
Chrisi_j

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top