View Full Version : Sum if Index Match with multiple criteria
jwerth
01-12-2017, 10:29 AM
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.
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
01-12-2017, 02:31 PM
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
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
01-13-2017, 08:53 AM
- 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.
(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
01-13-2017, 01:32 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.