Sum Duplicates and remove - add price/cost/stock

Hi Folks,

I need some help

The mfg provides us with a usage list for parts on machines, I need to tally the qty for each part, remove the duplicates and add the price/discount/stock data from another database.

First the usage list;

A,B,C,D are to be ignored

E=Item F=Description G=qty

I need to check if E is duplicated, if so then add qty's from G

after this I need the duplicate items (E) removed.

Example Before
E F G
002-1511-92 CAP 5
002-1511-92 CAP 5

Example After
E F G
002-1511-92 CAP 10

Now the price list

It's rather large so it's split into three sheets. 0-H , H-Q, Q-Z ( sorted by part number)

A = item B=Description C= Price D=Discount E=Available Stock F=ignored

If E from the usage list matches A from the price list then I need to harvest C,D,E and send to the usage list for the appropriate item. ( add columns to usage list with C,D,E data )

Hopefully someone can help!! This would be a very useful script!

Hello tada,

Welcome to the Forum!

You mention there are two sheets, DSSS1 and DSSS2. Is one of these the price list? Will the results be saved to a new worksheet or somewhere else?

Hello tada,

Glad you caught that error, but you didn't answer my question. It would be best for you to post the workbook for review.

One of the macros on my website is designed for this very thing, for the first part of your question.

Columns to Rows - Consolidation Macros - See Part 4

There's a sample file and the code you can tweak for your project to the correct columns.

Glad that code worked for you, eat all you want there, lots of useful merge/parse functions, I use them all.

I would expect an INDEX/MATCH() formula or a VLOOKUP() will work for your pricing issue. Both formulas work across workbooks, even if the 2nd wb is closed, which is nice. Read up on VLOOKUP, probably the simplest.

Once you get your formulas in the right place and working, it's pretty easy to convert "adding the formulas" into VBA code, if that's still necessary.

A formula approach, something like this in H2 on Sheet1:

=INDEX(Sheet2!C:C, MATCH($E2, Sheet2!$A:$A, 0))

Copy that to the right two columns, then down as needed. You should know if you got a good match as soon as you enter the first formula in H2.

You need to make sure the sheetnames are accurately noted in the formula.

Yes. Just complete the reference while both workbooks are open.

=INDEX([Data.xls]Sheet2!C:C, MATCH($E2, [Data.xls]Sheet2!$A:$A, 0))

Then, when you close the source wb, the formula will expand itself to include the full path, so it will keep working. Try it, you'll see.

NOTE: please consider NOT using the Quote button, you're duplicating my posts into yours for no gain, and it just clutters up your own thread. Use the Quick Reply button instead. The Quote is good for picking out something specific to discuss, but remove the rest.

Originally Posted by JBeaucaire

Columns to Rows - Consolidation Macros - See Part 4

Good example, JB, your site is growing nicely.

I must be doing this wrong

Please Login or Register  to view this content.

the file I want to check is DSSS.xls , I need to check 5 sheets on that file, they are named 1,2,3,4,5.

If I find a match from column B on my master list, with Column A on DSSS then I need to pull the values from C,D,E from DSSS

Originally Posted by tada

I need to return a TRUE or FALSE value in my sheet.

If the part number in Column A in sheet1 , matches Column J in sheet 5 then I need "TRUE" or "YES"

Working without a sample workbook...your question doesn't even make it clear which sheet you're wanting this formula on...so my guess would be this on Sheet1 for row 2:

=ISNUMBER(MATCH(A2, Sheet5!J:J, 0))
...should give you a TRUE/FALSE answer...copy down.

3D lookups are a tough one. I use a 3D VLOOKUP() technique, outlined completely on this page:

3D VLOOKUP

There's a sample file and an explanation of the formulas.

Yep, this one is a lot of work, typically.

Originally Posted by tada

Is it possible just to reference a xls file, and not the specific sheets?

No. Remote references must be complete.

Put this in A4 and then copy down as far as needed.

=IF(ISNUMBER(MATCH(B4,'110724'!J:J,0)), INDEX('110724'!B:B, MATCH(B4, '110724'!J:J, 0)), "")

Your question doesn't match the sample data you posted. Update with a better set of sample data and post again. Represent as much of the data as you can to show what you have to deal with, all the hurdles, and then your desired results.

This is a whole new creature.

Mark this thread as solved and post this up as a new question. You'll garner the attention of all the other contributors that look for new questions.

You should be prepared to accept a VBA solution, too, I imagine formulas might work, but they would be very intense and a LOT of them would most like cause performance issues with your workbook.

========
If your original need is resolved, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

If the PREFIX box is no longer available, just add [SOLVED] to the start of the title.

Not sure how to 'mark solved' on this one, sorry.

For that and a fount of other useful information, please see the forum rules.

Originally Posted by tada

Not sure how to 'mark solved' on this one, sorry.

Really? Post #31 does seem to have two very specific instruction/suggestions for you.