Sum if Index Match with multiple criteria

[jwerth]

Hello, I will use the below Col. 1, Col. 2, Col. 3, and Col. 4 Col 5 Col 6 as to provide an example of the problem I am having. I am having trouble organizing my sum if and index matches to where the code works the way I need it to.


Code executes only if the quantity in col. 5 DOES NOT ALREADY match to a quantity in column 2. If the prices in col. 4 and the colors in Col. 7 match one another, and the price in Col. 4 match with a price in col. 1 and the dates between the Col. 6 and Col. 3 match than sum the quantities in the two rows in col. 5 rows in an Index Match.

Capture.PNG

I hope this makes sense. If anything needs clarifying please let me know.

[SamT]

If the prices in col. 4 and the colors in Col. 7 match one another

If the Orange matches the Apple???

sum the quantities in the two rows in col. 5 rows in an Index Match.

???

If I had to guess, (I don't, but if I did,) I would think that you wanted a User Defined Function. I just can't tell what you need it to do

[jwerth]

I apologize for not being more specific. I meant that if black in row 1 matches black in row 2 and 10/30/2016 on row 1 matches 10/30/2016 in row 2 for that same column

[SamT]

I think you need to attach a sample workbook. Please supply enough various data to cover all possible scenarios. The sooner we know everything, the easier it is to get the code right.

Use the Go Advanced button and below the Advanced Editor, is a Manage Attachments button.

[jwerth]

- Okay, I have attached my actual worksheet and I will attempt to explain what I am trying to accomplish. Under column "Qty(CXL)" in sheet "Invoice" I would like "Qty" from sheet CXL to match to the amounts in each row to the column "Qty(Invoice)". (Basically an Index Match)

- My obstacle is that some rows in the sheet "CXL" are not grouped together in the same way that rows are grouped in sheet "Invoice" so I cannot do an Index Match based off of Qty and Price.

- In sheet "CXL" there are sometimes multiple rows that add up to equal one row on sheet "Invoice". Logically the only differences between the rows would be "Qty", or quantity and "Total". "Qty" is the column whose cells should be summed in the match on sheet "Invoice" under Qty(CXL).



The criteria for summing two rows should (I believe) be as follows so as to get a reliable match.

1. That if for sheet "Invoice" there is a row where the corresponding cells for that row in columns "Qty" and "Price" equal to the cells under columns "Qty" and "Price" for a row in sheet "CXL" than the cells under column "Qty" in sheet "CXL" stay in index match without summarizing.

2. If in sheet "CXL" the criteria mentioned in 1. is not true than the cells in the index match under column "Qty" for Sheet "CXL" will be summarized. For these cells in Qty to be added....

a)"Start" dates and "End" dates between rows in sheet "CXL" must be equal
b) "Location" between rows in sheet "CXL" must be equal
c) "Price" between rows in sheet "CXL" must be equal
d) The "Price" for the rows in sheet "CXL" that are being summed and matched to row "Qty(Invoice)" for sheet "Invoice" must all be equal to the "Price" in that row for sheet "Invoice". Than summarize the two cells in "Qty" For the match under column Qty(CXL) on sheet Invoice.

[SamT]

(Basically an Index Match)

Umm. . . No, it's not.
IndexMatch returns the index number of an array, (a Row or a Column) of the position in the array where a Match is found.

Let me see if I can accurately restate the issue.

Sheet "Invoice" contains a Summary of activity.
Sheet "CXL" Contains sub or partial components of the activity.
Locations, Prices, and Dates are the indicators that Sub-components on "CXL" are parts of a unique entry on "Invoice."

Question: Where is the result(s) of this code supposed to be placed?

Code Problem: The Locations on the two sheets are not identical and cannot be "Matched" as is. This table must be completed in order that the code can "Know" which Location on "Invoice" matches the ones in "CXL." Please add any Locations which are not present below.

Invoice	CXL		Unknown
APC-ACE			ALLIANCE
Cheyenne Hub Area			EP TEXAS
CIG-PAT	CIG		IROQUOIS
Consumers Energy CG	CONSUMERS		KERN RIVER
ENTERPRIS E WAHA			NGPL
ETC Tolar			NNG
Iroquois Z2			NWPL
Iroquois Z2 Gated			QUESTAR
KPGT-REC POOL			REX
LEBANON REX			SOCAL
M3			TETCO
Michcon Generic Pool	MICHCON		TGT
NGPL-Amarillo Pool	NGPL		TRAILBLAZER
NNG-Demarc Pool			WIC
NNG-TBPL Beatrice			WORSHAM STG
NNG-Ventura
Nrthrn Brder Ventura	NORTHERN BORDER
NWP-Wyoming Pool
PEPL-Pool	PEPL
PG&E-Topock	PGE
QPC-North
Socal- Citygate
Socal- Ehrenberg
Transco Z6 xNY North	TRANSCO
TRBL-WIC Dull Knife
TW-WTX Pool	TW
WRH into NWP

A different issue is that some cells use Hard Carriage Returns. It is too early to say that this will be a problem. It is important that the entries you make in this table, to be placed on Sheet "Cross_Index" are identical to the entries on "Invoice" and "CXL."

[jwerth]

You restated the issue perfectly. that is exactly what I need.

The "Location" is only identifying it as matching with another row within the "CXL" sheet since the location in a row must be = location in another row if the Qty is to be summarized. It is not matching to a location in sheet "Invoice"

To answer your question I thought I would need an index match thinking it would match in a column on sheet "Invoice" in column Qty(CXL) and either match exactly already, summarize and match, or not match #N/A to the amounts in the column Qty(Invoice). This would be similar to how the "Price" and "CXl Price" columns are next to it, which is an index match. The most unique identifier between the sheets "CXL" and "Invoice" is the price, this is what would be used in determining which "Qty" needs to be summed since the price is not enough a second criteria of having an equal start or end date between the sheets could be used.