+ Reply to Thread
Results 1 to 7 of 7

Need help creating macro for Inventory

  1. #1
    Registered User
    Join Date
    07-30-2012
    Location
    SC, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Need help creating macro for Inventory

    I am trying to write a macro that will take the Catalog ID Number and tell me what Location the product is in. I want to have one ID number and then list the locations next to it seperated by semicolons.

    Here is an example.


    Location Catalog Item ID
    STORERM 009897
    STORERM 013070
    STORERM 014057
    STORERM 016493
    STORERM 016568
    STORERM 016926
    STORERM 018570
    STORERM 018953
    STORERM 018996
    STORERM 019046
    STORERM 019062
    STORERM 021476
    STORERM 022224
    STORERM 027025
    STORERM 027261
    STORERM 032920
    STORERM 033084
    STORERM 038679
    STORERM 041796
    STORERM 047880
    STORERM 047884
    STORERM 047889
    STORERM 047895
    STORERM 048048
    STORERM 048049
    STORERM 048058
    STORERM 048062
    STORERM 049451
    STORERM 063798
    STORERM 070408
    STORERM 070440
    STORERM 070564
    STORERM 070599
    STORERM 071021
    STORERM 071048
    STORERM 071196
    STORERM 071242
    STORERM 071390
    STORERM 075426
    STORERM 075620
    STORERM 083369
    STORERM 084285
    STORERM 085168
    STORERM 090389
    STORERM 092839
    STORERM 092840
    STORERM 092841
    STORERM 092842
    STORERM 093126
    STORERM 093127
    STORERM 093166
    STORERM 093178
    STORERM 093180
    STORERM 102285
    STORERM 116378
    STORERM 116379
    RM01 125781
    RM01 125781
    RM01 125781
    RM01 125781
    STORERM 125781
    STORERM 129866
    STORERM 130637
    STORERM 130752
    STORERM 139791
    STORERM 143893
    STORERM 145793
    STORERM 146269
    STORERM 147019
    STORERM 153427
    STORERM 156908
    STORERM 157418
    STORERM 157442
    STORERM 157450
    STORERM 157536
    STORERM 157541
    STORERM 157590
    STORERM 161775
    STORERM 162848
    STORERM 167212
    STORERM 167213
    STORERM 167449
    STORERM 167955
    AS01 169535
    AS01 169535
    AS01 169535
    HS01 169535
    RM01 169535
    STORERM 169535
    SW01 169535
    SW02 169535
    SW03 169535
    STORERM 169775
    STORERM 170791
    STORERM 170802
    STORERM 175253
    STORERM 177485
    STORERM 179480
    STORERM 187384
    STORERM 188813
    STORERM 190481
    STORERM 200017
    STORERM 200020
    STORERM 200021
    STORERM 200024
    STORERM 200026
    SW02 200026
    STORERM 200027
    STORERM 200033
    STORERM 200034
    STORERM 200035
    HS01 200036
    STORERM 200036
    STORERM 200042
    STORERM 200047
    STORERM 200049
    STORERM 200065
    STORERM 200070
    STORERM 200071
    STORERM 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078
    SW03 200078


    Thanks for help.

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Need help creating macro for Inventory

    Could you explain just a little more? I'm not quite understanding what you want to do. Sorry

  3. #3
    Registered User
    Join Date
    07-30-2012
    Location
    SC, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Need help creating macro for Inventory

    Thanks for reply.

    Take this part for example.

    AS01 169535
    AS01 169535
    AS01 169535
    HS01 169535
    RM01 169535
    STORERM 169535
    SW01 169535
    SW02 169535
    SW03 169535

    I want the end result to look like

    169535 / AS01;HS01;RS01;STORERM;SW01;SW02;SW03

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need help creating macro for Inventory

    Hi zachm85 and welcome to the forum,

    Find the attached with two possible methods for finding where the heck those parts are located. It is better if you don't use the format you suggest as it will complicate the spreadsheet in the future.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    07-30-2012
    Location
    SC, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Need help creating macro for Inventory

    Thanks , thats what I originally had planned to, but the customer wants it that way. Here is the file that I am working with.

    Any ideas on how to consolidate into a T Chart almost?

    Thanks for help.
    Attached Files Attached Files

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need help creating macro for Inventory

    Hi Zach,

    I did a pivot on your data and see lots of duplicates. IE - part 265002 has 49 different locations (by count) when there are only 7 possible locations. That means you should first delete duplicates in the data before anything else. There are helper columns that would allow you to build the table you need but without deleting duplicates there would be up to 49 locations behind part numbers in this format:::
    265002 / HS01, HS01, HS01, .... SW01. Bad news.

    Try to get the customer to do part 263180 by hand to show them they know not what they ask for and how a Pivot is much better.

    See the attached and then sort it by Part ID to make it look better.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Need help creating macro for Inventory

    Try the attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1