I have a sheet called "Usage Data" and in that sheet Column A is called "DTL_LOC" (Warehouse #)
I need to pull all data from this column and create a list with no duplicates. Data is both Text & Numeric.
I need to use this information in a Validation List on my "Inventory" sheet in C1
The list is currently located in L3:L102 on the "Inventory" sheet. However this list may increase or decrease as the company grows.
Thank you,
Mike
Last edited by fasterthanyours; 08-12-2009 at 04:18 PM.
see my post here
http://www.excelforum.com/excel-gene...tion-list.html
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
The Pivot table doesn't work, because it does not account for growth of the list.
I need something that will check every cell except A1 in Column A and compile the list without duplicates
I attempted to modify ContaminatedWitExcel's formula
http://www.excelforum.com/2092758-post7.html
To work with my workbook, but all I get is a bunch of #'s
Please Login or Register to view this content.
Please Login or Register to view this content.
If there are no blanks interspersed at source and you move first row of named range to C2 rather than C1 you could perhaps use this non-array approach:
C1: =SUMPRODUCT(--('Usage Data'!$L$3:$L$102<>""),1/COUNTIF('Usage Data'!$L$3:$L$102,'Usage Data'!$L$3:$L$102&""))
Inventory Sheet
C2: =IF(ROWS(C$2:C2)>$C$1,"",INDEX('Usage Data'!$L$3:$L$102,MATCH(TRUE,INDEX(ISNA(MATCH('Usage Data'!$L$3:$L$102,$C$1:$C1,0)),0),0)))
copied down as far as required
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi,
Paste this code in 'Inventory' sheet module
HTHPlease Login or Register to view this content.
Kris
DUDE! That's F'ING AMAZING!!!!Originally Posted by Krishnakumar
Thank you so much!!!
None of the others were working correctly...
I would attached the example, but it's 4MB :-)
Last edited by fasterthanyours; 07-27-2009 at 01:25 PM.
oh yes it does you just have to refresh itThe Pivot table doesn't work, because it does not account for growth of the list
Zip can do amazing things these days
If you find the response helpful please click the scales in the blue bar above and rate it
If you don't like the response, don't bother with the scales, they are not for you
Funny LOL Was scatter brained that day.Zip can do amazing things these days
Krishnakumar,
I need your help again.
The Script worked great, but I decide that the Data on the "Usage" sheet need to be in Column C. I tried Modifying your script, but I can't get it to work right.
Hi,
Replace
withPlease Login or Register to view this content.
HTHPlease Login or Register to view this content.
Cool thanks.
Works now.
Would there be a way to Compile a List of both ITEM & DESC columns from Onhand and Usage Data without duplicates?
Could you post some sample data?
Yes, took me a minute, but here you go.
Currently the Vendor # DESC1 and DESC2 are pasted in from our Price List Spreadsheet in house.
I'd like to compile the Inventory List from the Usage and On Hand Sheets and do away with the DESC2 possibly.
Thanks
Hi,
In standard module
and call this macro at the end of Worksheet_Activate subPlease Login or Register to view this content.
i.e.
HTHPlease Login or Register to view this content.
I put that in the Inventory Sheet?
Kris? Where should I put your code?
I figured it out. Works great. Found out I had some bad data as well.
Thanks
Kris
Got a new requirement from the boss.
I have entries in my ITEM Columns for thing like:
23022
23022LH
23022LH
23022RH
23022RH
2716
2761LH
2761LH
2761RH
2761RH
3059
3059B
3059B
3059B
3059L
3059L
3059L
3062
3062B
3062B
3062B
3062B
3062B
3062L
3062L
3062L
3317
3317HH
3317HH
3317HH
3317KC
3317KC
3438
3438LH
3438LH
3438LH
3438LHR
3438LHR
3438RH
3438RH
3438RH
3438RH
3438RHR
3438RHR
3438RHR
3438RHR
3515
3515LH
3515LH
3515RH
3515RH
3515RH
3519
3519LH
3519LH
3519LHR
3519LHR
3519RHR
3519RHR
3520
3520D
3520D
3520M
3520M
3520M
3520M
3559
3559LH
3559LH
3559LHR
3559LHR
3559LHR
3559LHR
3559LHR
3559RH
3559RH
3559RH
3559RH
3559RHR
3559RHR
6566
6566RHR
6566RHR
6569
6569LH
6569LH
6569LH
6569LH
6569LH
6569LH
6569LH
6569RH
6569RH
6569RH
6569RH
6569RH
6569RH
6569RH
6569RH
6569RH
6569RH
6573
6573LH
6573LH
6573LH
6573LHR
6573LHR
6573RH
6573RH
6573RHR
6574
6574RH
7910
7910LH
7910LH
7910LH
7910RH
7910RH
7910RH
7910RH
7910RH
7910RH
7910RH
7910RH
7910RH
What he'd like to do is combine all of the ####LH & ####RH, etc into just ####. You can also see that I have some entries that are 5 #'s long as well. What he needs is to combine the 7910, 7910LH, & 7910RH as one entry and then I need to Inventory Sheet total the Onhand and Used for all entries with the same Item # regardless of text.
Would your script be able to do that?
Thanks
Hi,
Try this amended code.
And your formula would bePlease Login or Register to view this content.
In F3 and copied down,
=IFERROR(SUMIF(Onhand!$H:$H,$C$1&"-"&B3&"*",Onhand!$D:$D),0)
In G3 and copied down,
=IFERROR(SUMIF('Usage Data'!$N:$N,$C$1 & "-" & B3&"*", 'Usage Data'!$L:$L),0)
HTH
Do you Mean E3 for Onhand Data and F3 for Usage Data?
Also Your combine $C$1 and B3, B3 is the Description Field. Item # is A3. So shouldn't it be A3?
Either way, it didn't work for me.
Very Frustrated right now. Thanks for you help.
I attached my sample sheet again Maybe that will help?
I figured out away to convert the 3062LH and such to 3062 and so forth for every Line ITEM. I just couldn't figure out how to make it count or SUM them all for each Location/Warehouse. I assume that's what you were trying to do with script?
See if this helps you.
Thanks looks good.
I would like it to Compile the Data every time though. Can I just move your code into the Active Sheet Sub?
Notice something else. The list stops around Row 839 and then starts up again at Row 881. Any idea why?
Looks like when the Compiled Data Switches over to the "None On Hand" Inventory it bumps down a few Rows for either warehouse.
Last edited by fasterthanyours; 08-05-2009 at 08:28 AM.
Kris,
I still need some help with this please.
Hi,
Find attached.
HTH
This is working perfectly now.
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks