VBA or formula. match, index and sumproduct

**rain4u** · 09-17-2012, 07:12 PM

Hi all
I would be very thankful if someone can help me out. I'm after a VBA or formula solution, whichever will do the job. Essentially the formula or VBA would need to match the the Item "S2:S11" with the headers "F1:Q1" and populate the the "sum" of the respective matched column in "T2:T11".

Below is the way I have the data/table in "F1:Q39"

draft	DWIP	DREJ	DRET	DREF	DCAN	DERP	min. d.	int. rej.	rework	scrap	sup. rej.
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	0	0	0	0	0	1	0	0	0	0	0
0	0	0	0	0	0	1	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0

Now on the same sheet I would like to populate the following table ("S1:T8") below:

Item	sum
draft
DWIP
DREJ
DRET
DERP
DCAN
scrap

So after the VBA or formula it would look like below:

Item	sum
draft	8
DWIP	20
DREJ	8
DRET	0
DERP	2
DCAN	0
scrap	0

I was playing around with match, index, offset and sumproduct but did not really get anywhere. I would not want to use pivot tables if possible. Items in S2:S11 can be pretty much any one from those headers and the order may also vary.
I have attached workbook with sheets "Before" and "After"

I would appreciate if someone can help me out!
Cheers
Rain

**ConneXionLost** · 09-17-2012, 07:27 PM

Try this:

Please Login or Register  to view this content.

**xladept** · 09-17-2012, 07:37 PM

Hi rain4U,

Here's a VBA routine:

Please Login or Register  to view this content.

**stanleydgromjr** · 09-17-2012, 07:52 PM

rain4u,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SumItemColumn macro.

**rain4u** · 09-18-2012, 04:42 AM

Cheers for all of the answers. Much appreciated. I went with Stanley's solution, though all of these could do the job.
Rain

**xladept** · 09-18-2012, 12:51 PM

You're welcome!

**stanleydgromjr** · 09-18-2012, 02:46 PM

rain4u,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

VBA or formula. match, index and sumproduct

LinkBack

Thread Tools

Rate This Thread

Display

VBA or formula. match, index and sumproduct

Re: VBA or formula. match, index and sumproduct

Re: VBA or formula. match, index and sumproduct

Re: VBA or formula. match, index and sumproduct

Re: VBA or formula. match, index and sumproduct

Re: VBA or formula. match, index and sumproduct

Re: VBA or formula. match, index and sumproduct

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions