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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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:
Upvote 0
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
 
Upvote 0
Actually you just need the advanced filter, no pivot table, to do this:


Excel 2010
AB
1SkuOrder Date
28036726/23/2014
32606446/23/2014
45246016/23/2014
5B101070046/23/2014
6B61350BY46/23/2014
74032856/23/2014
8B61350BY46/23/2014
9B61350BY46/23/2014
106459246/23/2014
116335956/23/2014
12
13Sku
14B61350BY4
15403285
16645924
Sheet1


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


Excel 2010
AB
1SkuOrder Date
6B61350BY46/23/2014
74032856/23/2014
8B61350BY46/23/2014
9B61350BY46/23/2014
106459246/23/2014
12
13Sku
14B61350BY4
15403285
16645924
Sheet1
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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
Back
Top