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.

18013

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.