Vlookup And Matching 2 Criteria

Dec 23, 2008

I have an output table that looks like the followings:......

There is 1 input for the spreadsheet: What month? Enter 9, 10, 11 etc. I need it to pull from a data sheet that is set up as such:......

So effectively it needs to vlookup the name in column A, (A, B, or C), and then it needs to match that to the column that has the same name as the output table (Crit.1 or Crit.2 and the number of the month above it (10,11,etc). I attached an excel sheet to better demonstrate

ADVERTISEMENT

Returning Data From Separate Worksheet By Matching Multiple Criteria - Vlookup / If Combo?

Jun 8, 2014

I am attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.

The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.

View 2 Replies View Related

Matching First 4 / 5 / 6 Or 7 Words Via VLookup

Feb 12, 2014

I am trying to match the first few words in a spreadsheet with another column via vlookup. I tried [URL] ..... but it didn't work.

see attached spreadsheet. If column A and column N match up, I need a YES in column, otherwise a NO. I need formulas for the 4 first words matching, 5 words matching etc.

View 4 Replies View Related

Vlookup Matching And Copy The Value

Dec 17, 2009

I tyring to match column A:B with column F:G and copy the value in column H to column C.

View 3 Replies View Related

VLookup - Matching Two Cells?

Dec 8, 2011

Is it possible to do the following?

Worksheet1!A2 holds a text value
Worksheet1!B2 holds a text value

Worksheet2!D2 holds a text value
Worksheet2!C2 holds a text value

Is it possible to use vlookup to find a match for Worksheet1!B2 in Worksheet2!C2 where the combination of Worksheet1!A2, Worksheet1!B2 is the same as the combination of Worksheet2!D2, Worksheet2!C2?

View 9 Replies View Related

Add Two Matching Values Using Vlookup

Sep 17, 2009

How can i add a value suppose 10 which is in cell A2, now i use vlookup to search
lookup_value=A2(i.e.10),
Table_array=column c,
Column_index_number=1 and
range_lookup=0

so when vlookup finds the matching value the sum should be 10+10=20
also it should match -10, so that sum will be 0.

View 9 Replies View Related

Keyword Matching And Multiple VLOOKUP

Dec 6, 2012

I am looking to automatically assign products to a relevant categories on my website and I am working on a spreadsheet. Each category is has a four digit number assigned to it and categories are limited to 3 (Primary, Secondary & Tipperary)

For example:

Computers - 1000 - Desktops - 1100 - Dual Core PC - 1101
Computers - 1000 - Desktops - 1100 - Quad Core PC - 1102

So as you can see you can tell a Dual Core PC is in the Computer & Desktop categories just by looking at the first two digits. The last two digits are the category number. 01, 02, 03 etc.

What I wish to do is associate keywords to the category determine which category that product should be assigned.

Product ID
Product Title
Product Description
Product Spec1
Product Spec2

1
HP Desktop PC
HP Desktop PC's are computers
Intel Dual Core Processor
500GB HDD

2
HP Desktop PC
HP Desktop PC's are computers
Intel Quad Core Processor
1TB HDD

Keywords assigned to category 1101 are Computers, Computer, PC, Desktop, Dual Core.
Keywords assigned to category 1102 are Computers, Computer, PC, Desktop, Quad Core.

And the spreadsheet looks like this:

The script/sum would look at all the cells and match the highest count of keywords to that particular category and populate additional fields called Cat1, Cat2 & Cat3. Therefore the final spreadsheet would look like this:

Product ID
Product Title
Product Description
Product Spec1
Product Spec2
Cat1
Cat2
Cat3

1
HP Desktop PC
HP Desktop PC's are computers
Intel Dual Core Processor
500GB HDD
1000
1100
1101

2
HP Desktop PC
HP Desktop PC's are computers
Intel Quad Core Processor
1TB HDD
1000
1100
1102

How this can be implemented? possibility with the use of VLOOKUP or MATCH commands?

I believe a vlookup would be fine but there's was only 1 value to match but as it's multiples it would need to prioritise based on the number of keywords matched and not just the first match found.

[URL] .......

View 4 Replies View Related

Vlookup (matching Phrases For Values)

Jul 5, 2007

I am doing a project where I have two lists, one list is much larger than the other but has more info. I need some of that info for my other list, so I have used vlookup, but it hasn't helped.

The formula that I am using is
=VLOOKUP(B2,[Report.htm]report1183662451726!$A:$B,2,FALSE)

now the value I am searching for is "ANOS" which I know is in the search column, but the answer when I search is #NA. When I take out false the right value will fill in fro "ANOS", but if two words are similar such as "ALLN" and "ALEX" then it will give the same values for both which isn't right.

View 9 Replies View Related

Vlookup Of Partial Matching Values

May 6, 2007

I have a worksheet with 2 tabs. Tab 1 has data in two columns, column A contains a part number (6 characters long) and column B contains a value for each part number.

Tab 2 is a sheet with multiple colums of data. I need tab 2 to look at the data in tab 1 and display the value of each part number in a specific column that has corresponding part numbers but are 15 characters in length. The part numbers in tab 1 are the last 6 characters of the part numbers in tab 2.

I have been trying to use vlookup in tab 2 but it won't work because the column of part numbers in tab 2 is a full part number and the part numbers in tab 1 are only a partial part number (last 6 characters of part number in tab 2).

Tab 1 is a worksheet that is created from a Access query of a sql database. I cannot control the abbreviated part number that the query generates.

Tab 2 part numbers (the full 15 character number) also is generated from another access query which I also can't control.

Is there any way I can have tab 2 sheet look at the data in tab 1 and display the values for the part numbers in tab 2.

View 9 Replies View Related

Excel 2010 :: VLookup To Return 2nd Matching Value

Mar 10, 2012

Is is possible to construct a vlookup to identify that there are duplicate values in the Lookup_value and the Table_array and return the second and third values in the Formula result?

In the acutal project the Lookup_values and Table_arrays are in separate worksheets, but I think the example below represents the issue I'm working with.

I'm using Excel 2010

ABCDEFG1=SUM(IF($A$2:$A$8=A2,1,0))=VLOOKUP(F2,$A$2:$B$8,2,FALSE)
2JOHN13BILL12BILL11BOB14MARY11FRED15JOHN23JOHN16FRED11JOHN17BOB11JOHN18JOHN33MARY1

View 9 Replies View Related

Lookups Matching 2 Criteria

Dec 23, 2009

If you look at my attached example. I'd like the date line/columns in Sheet "8002372008" to be populated automatically with data from Sheet "Data".

Is this possible? VLOOKUPs and MATCH are what I was thinking, but I'm not sure how to do it.

I know it could probably be done in VB, but I don't want to have to click on each "800 Number" sheet to update it every time, so that my summary sheets are accurate.

View 10 Replies View Related

Matching Data With Specified Criteria

Jun 30, 2007

I need to match data with specific criteria. Details of the problem are as follows:

I have 2 sheets: "Main" and "Sub"

In "Main" there are 3 Important Columns: Col "C", "I" and "AF"
In "Sub" there are 2 Important Columns: Col "I' and "N".

Value in column "C" of "Main" is same as value in column "I" of "Sub", but not in same order.

Now the specific criteria:

If value in Column "I" of "Main" is not equal to "Zero", then the macro should pick up the value from the corresponding column "C" of "Main" and search for the same in column "I" of "Sub". After matching the same, it should find corresponding value in column "N" of "Sub". Then the macro should pick up the value from column "N" of "Sub" and put the value in the column "AF" of "Main".

This is basic requirement.

I am unable to retrieve the right value for the same.

Fine tuining will be:

If the value in column "N" of "Sub" is "Fully Automated" then it should only write "Automated" in column "AF" of "Main". And If the value is "Manual + ...." then it should write "Manual" in column "AF" of "Main".

View 14 Replies View Related

How Can Find A Value Using Two Different Matching Criteria

Feb 9, 2006

Col cCol dFormula
Lease TermCompany #6023%
3637%
4843%
6052%
12620%
247FALSE
368FALSE
48105%
609FALSE

Residual Table

Company #
127
438
569
10

Col b
Lease TermResidual %
1210%20%9%
245%9%6%
364%7%3%
483%5%2%
602%3%1% ......

View 10 Replies View Related

Multiple Criteria Matching In VBA?

Apr 22, 2012

Modifying the following line by replacing the row number in AC2 and Q2 by an incremental variable? I mean something like “AC”&Rw , “Q”&Rw, &Rw, Rw being my incremental variable used to go through the rows.

ElxJobNo=Evaluate("Index(Index(Jobnumbers,0,1),Match(AC2&Q2,Index(Jobnumbers,0,3)&Index(Jobnumbers,0,4),0))")

View 3 Replies View Related

Sum Based On Matching Criteria

Jul 6, 2006

How do I return an offset value within a named range using a formula? For example, my range "RngTest" is from C3:J43. If a value of "Product123" is found within the range, (lets say its found in cells C3; E5 and E7), I need the accumulative values in the cells 'below' (in cells D3; F5 and F7) added ( SUM) and returned to cell L3.
Therefore if Cells D3; F5; and F7 have the respective values of 3; 4 and 5,
the value of cell L3 should be 12. Also, do I need to express "RngTest" or "C3:J43" or does it matter?

View 4 Replies View Related

Results From Two Matching Criteria

Mar 20, 2007

I am trying to solve a small problem that i have, I am not that good with VBA and need somehelp.
heres the situation.
Sheet 1 is filled in col.1= title, col.2= table# (ex. 1,2,...11)col3.= grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10). I will enter a title then pick a table then a grid location.
On sheet 2 also known as table 1. col.1 =grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10) col. 2 = title.

I want to create a macro or function that can evaluate sheet1 and pick out when col.2=table 1 and col.3 = grid loc. A-1.... through D-10 then give me what is in col.1.
this will have to be done for 11 sheets 1 sheet for each table.

View 9 Replies View Related

Multi Text Matching Criteria

Apr 5, 2013

I'm trying to build a formula on the matching text as below, im getting an value error, however when i press the FX key it shows the correct result.

Column A
Column B
Column C
Column D

[Code] ....

( I would like to match the below two criteria with the Row 1 and Row 2)

Co1

#VALUE!

Latest Forecast

Formula-->
IF(MATCH(A6&A7,B1:I1&B2:I2,0),"Ok","Not ok")

View 1 Replies View Related

Matching Multiple Criteria In SUMIF?

Jan 30, 2014

I'm trying to create an overview which shows a breakdown of the total minutes for each task on each given day.

I need to get a formula to work in the grey area which sums the total minutes (column C) based on matching two criteras of task (column A) and day (column b).

View 5 Replies View Related

Three Matching Criteria Of Data, Is Sumproduct Appropriate?

Jan 30, 2009

I have two sheets where I dump data into. Widget sales and Widget Accessory sales. I'm trying to display for the widget salesmen the amount of dollars they sell in widget accessories ONLY for the transactions in which they also sell a widget at the same time.

So I need a formula or process (taking and comparing the data from both sheets) to do the following

IF
1. The dates of a widget sale and the accessory sale match (in both sheets)
2. The salesman ID in both sheets match
3. The names of the customer in both sheets match
THEN
1. Add up the total dollars by salesman for sales that match the above criteria

I've tried using sumproduct... I've tried using countif and concatenating pieces of data to make unique sales codes... I've exhausted my meager excel skills .

View 4 Replies View Related

Fetching Cells Per Row Matching Two Criteria

Jan 8, 2014

I have a sheet containing a list of client name,the date they've been assigned, the type of account they have, the status of the account, and the last update date.

What I'm trying to accomplish is to extract the client name and the type of account on that table that shows as "complete" in status. However, it gets complicated since I need to separate the data that had been 'completed' under the same month, with those completed the other month.

For example, I have been assigned 2 clients this January. I closed one in the same month, and the other closed at February. The first client would be listed under the first table (Completed within the month) and the other would be on the second (Completed on previous months)

Since this would be monthly, I am thinking if it would be more efficient to include the table per sheet (as one sheet corresponds to a month), or consolidate everything to a single sheet... I'm not sure which one would be best.

View 2 Replies View Related

Matching Customers And Excluding From Criteria

Aug 31, 2013

I need to compile a 'sumifs' formula to add sales for the salesmen.

Below is an example table. My dilemma is determining that in addition to the sales I only want to 'sum' the 'CANCEL' sale of a customer that initially purchased a 'GOOD' Status and not a 'PENDING' status.

How can i write a formula to determine that (for example) Customer3 'cancelled' the 'pending' sale, therefore I do not want the 'cancel' to sum in my calculations.

Date Salesman Customer Product Status Sale
Jan 8th John Customer1 Apples Good $500
Jan 9th Mark Customer2 Pears Good $200
Jan 8th Kevin Customer3 Oranges Pending $250
Jan 15th Mark Customer2 Pears Cancel $200
Jan 16th Kevin Customer3 Oranges Cancel $250

View 4 Replies View Related

Matching Multiple Criteria And + / - Numbers

Jun 28, 2014

I have a spreadsheet where I want to match the PO info with the invoice info. I want to match the cost center, location, PO number, item number and the price.

Period
Cost Center
Account
Item No
PO No
Amount
Test

DEC-12
5000AF
000
M0002648
13579
(59.89)
5.11

[code]....

The formula I used in the test column

=SUMPRODUCT(--($B$2:$B$13&$C$2:$C$13&$D$2:$D$13&$E$2:$E$13=B2&C2&D2&E2),$F$2:$F$13)

As you can see in that the formula is showing the first 4 rows with a difference even though that the difference is in the first two. How can i fix this?

My second question, on the lines with the 989.04 difference, how can I make the same formula to say "Fix the account" or to fix the column with discrepancy

View 4 Replies View Related

Copy Rows Matching Criteria

Jan 3, 2007

setup a vba code to find values which were separated by a certain value, thus there would be pairs of numbers sought after. What occured was that the first value is bolded, followed by the second not bolded, this pair would be separated by 75. Now I want to apply the same code, but instead of copying just the two cells which were separated to someone on the current sheet, I would like it to copy the entire row (as there is more data associated with it now) with the same bold/not bolded pairing to a new sheet, called "Numbers". I would imgine it be straight forward, yet I am very new to this and still tinkering.

Heres the ....

View 9 Replies View Related

Delete Rows Not Matching Criteria

May 11, 2007

I have been using the code found here

Sub DeleteRowsFastest()
Dim rTable As Range
Dim lCol As Long
Dim vCriteria

On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else.............................

to delete rows that match the given criteria. I am now wanting to do the opposite, keep the rows matching my given criteria and delete all others.

View 3 Replies View Related

Count Matching Multiple Criteria

Jun 25, 2007

I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got

View 3 Replies View Related

VLookup Based On 2 Columns Matching To Give 3rd Column As Answer

Dec 27, 2012

I am trying to created a spreadsheet for work where I have created to validation drop down boxes, one each box has been selected i want it to return back with the correct answer in the 3rd column.

below are the 3 colums. i have created a validation for column 1 and 2 but when selected i want the final box to = column 3 ie. >=9, =2

120%

12
>=2
130%

13
>=2
140%

[code].....

View 9 Replies View Related

Combine Rows From Matching Certain Criteria In Two Tables

Jun 9, 2013

I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.

A sample file illustrating the inputs and required output is attached.

I also posted this on: [URL] ....

Sample_file_summary.xlsx

View 2 Replies View Related

Lookup Data With Matching Criteria String?

May 2, 2014

how to figure it out this lookup problem (lookup using partial string of match)...

View 5 Replies View Related

Finding Row On Matrix Matching 3 Criteria And Get A Result?

Dec 11, 2012

The matrix:

Col A Col B Col C
2012-11-08 07:26:16Check-InJohn
2012-11-08 13:23:58Check-OutJohn
2012-11-08 13:24:01Check-InJohn

The goal:

Since i have a given:

1. Name(which exists in column C)

2. Date (which exists in column A - formated has (AAAA-MM-SS HH:MM:SS)

3. Event (which exists in column B)

4. Hour*

I want to know the nearest hour that matches all criterias.

In the example on top, if i give:
John, 2012-11-08, Check-In, 14:00:00
I want the result: 13:24:01

If, for example, the date doesn't exist, the result could be a simple error message.

View 3 Replies View Related

Matching Multiple Criteria On Row Then Showing Cell On That Row?

Feb 3, 2013

I've got an array of data about 30x1500 cells in size, I want to use this data in a table I'm making on another worksheet.

Normally I'd just use VLOOKUP and this works great in most cases but I want to do something a little bit more complicated. Basically I want to (in normal English) have excel look at the table, find a cell that matches criteria in this data array, then compare to another cell on the same row in this array. If the criteria for these two match then display the data from another cell on the same row. The criteria for matching the first cells would be an exact value, both being strings of text.

I've tried using a couple of nested VLOOKUP and IF statements but failed .....

View 4 Replies View Related

Matching Criteria From A Virtual ( Non-Existing Range)

Dec 10, 2008

Matching criteria from a Virtual ( Non-Existing Range)

I need help on two things:
1> Virtual Range
2> Displaying TRUE or FALSE

I want to perform a Lookup Using the Regular Match Function with the following Syntax for checking Dates falling in a Fixed Range...

View 14 Replies View Related

How To Use SUMIF On Consecutive Cells Matching Criteria

May 28, 2009

How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80)

=IF(A81<>A80,SUMIF(A$5:A80,A80,H$5:H80),"")

More data:

Column A is a group of dates, but not in order, they are slightly mixed up
Column H is a monetary value.

View 8 Replies View Related

Sum Index Results Matching Multiple Criteria

Jul 14, 2009

I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter.

HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity.

C is the part number column in the schedule
K is the date row in the schedule ....

View 10 Replies View Related

Extract Cell Value From Sheet After Matching Certain Criteria?

Aug 9, 2012

I have two excel sheets. Lets say one is for May and another for June month. I have some data like name of account, account number their ratings . I wish to find if the account present in June month was present in May month sheet or not and if yes, then i need to extract a rating entry( column) from it and paste it in another sheet where i have copied all the data of June month so that i am able to see if the rating has changed over the months or remain same for that particular account...

i am trying vlookup but it is not able to search the name of the account even when it is present in the sheet( like WBIDFC) ... it seems the problem may be because of the format but i am not sure.... moreover i tried to vlookup via account no. but problem is that sometimes the account number is given and sometimes not.. Moreover , the format also does not match sometimes..

View 9 Replies View Related

Matching Multiple Criteria On Separate Tab To A Matrix

Apr 25, 2013

Currently I have two tabs one with six columns of data. On a separate tab I have a matrix of two of the criteria. I would like to map the company data to the matrix, but recognizing the primary representative has more than one company he/she manages. Hence I don't just need the first entry, but all of his/hers maps to the size columns.

View 5 Replies View Related

Pivot Table - Keep Only Rows Matching Criteria

Jul 21, 2014

adv bar

a11 b3
a2 b3
a3 b5
a4 b6
a5 b8

I have a pivot like the one above. I want to keep only the rows if "bar' falls within a range. So in a certain range (say d2:d7) I set the criteria for "bar". Is there a way to tell the table to keep only the rows, if the "bar" value can be found in d2:d7?

View 2 Replies View Related

Array With Values From Cells Matching A Criteria

Oct 3, 2009

How do you load an array with values from cells matching a criteria?

I want to look for the number 1 in row 5 and put the Adjacent values in Row 2 in an array.
So here my array should have 1,3,6

Dim CountOne As Integer
CountOne = Application.WorksheetFunction.CountIf(Worksheets("Tool Administration Form").Range("B8:AE8"), "1")

Dim All As Variant
Dim rCell As Range
Dim ArrCnt As Integer
Dim lArrVar As Long
ArrVar = 0

Do
For Each rCell In Worksheets("Tool Administration Form").Range("B8:AE8")
If rCell = 1 Then
ArrCnt = ArrCnt + 1
All = Cells(-3, 0).Value
End If
Next rCell

Loop Until ArrCnt = CountOne
I keep getting errors... I don' tknow how to fill the array properly wiht the values in the cells.

Example:
A B C D E F G
1
2 1 2 3 4 5 6 7
3
4
5 1 2 1 3 3 2 1 4

View 9 Replies View Related

Delete Rows Not Matching 3 Static Criteria

Sep 12, 2008

I have a worksheet with 20,000 plus rows, header=yes, and I need to delete all rows NOT containing the numbers 6600, 6700 or 6800 in column C. I'm currently using the below code which works, but is DOG slow.

Sub TestDeleteRows()

Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
End With

View 5 Replies View Related

Move Worksheets To Workbook Matching A Criteria

Aug 14, 2006

What I want to try and do is move certain worksheets if their name is changed into another workbook. So there are two scenarios:

1. If any worksheets name is changed to begin with "Old", then it is automatically moved to a workbook called "OldPlans"
2. If any worksheets name is changed to begin with "Expired", then it is automatically moved to a workbook called "ExpiredPlans"

When worksheets are moved to new workbook I would like them to be positioned towards the end of the workbook. So I am not sure how to go about this, first would it have to have a Name Change Event or something to trigger off what gets moved?

View 5 Replies View Related

Find Rows With Cells Matching Criteria

Nov 14, 2006

I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet.

The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,)

Step 1:
"Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E".

Step 2:......................

View 5 Replies View Related

Adding To Summarize Values After Matching Criteria

Apr 26, 2007

I have three worksheets. The first one has a 20 digit code that is broken into eight sections ( cells) and an amount assign to it. There are currently 150 codes but can grow bigger as new codes are assigned with their own amount. The other two worksheets have the same breakdown for the code but will be filled in as needed. They will record additions or subtractions to the code, like a register. I would like to then summarize (on a fourth worksheet) the +/- activity from worksheet 2 and 3. To do this I will copy the first worksheet then setup three additional columns one will be Increase the other Decrease and the last one Balance. What I will need to make sure that I only pick up the amount for the right code. So I need to create a formula that makes sure that it compares wks 2, cell A1 to wks 4, cell A1 AND wks 2 cell B1 to wks 4, cell B1 AND wks 2, cell C1 to wks 4, cell C1 and so fourth until the eighth breakdown of the code. When this is true then I will need to add the amount that was recorded on wk2 or wk3 for that code on wk4. The codes may be used various times so the sum of the values of wk2 or wks3 will need to be cumulative.

I've been reading about Sumproduct and I have created this formula but the result is # NUM....

View 9 Replies View Related

ADVERTISEMENT