Return Multiple Values Across Columns And Rows Based On Criteria

Jun 20, 2008

I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.

EG

Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50

ADVERTISEMENT

Array Formula To Return Multiple Values Based On Various Criteria?

Dec 19, 2013

I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....

My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)

I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.

View 4 Replies View Related

Formula/Function To Return Multiple Values Based On Criteria

Aug 25, 2006

I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?

View 2 Replies View Related

Return Multiple Values From Columns To Rows Between 2 Dates

Feb 8, 2013

I got a good start on what I need to do from this thread here: [URL] ......

A user will use a userform to enter in results from a room inspection into one sheet and then on another sheet selects the maid and it pull up the matching room inspections. I wish to then limit it to a date range which can be found in two cells.

Currently cells D2:H2 contain the array

[Code] ......

and cells D3:H3 contain

[Code] .........

I would like to further limit those searches by restricting the date range, Cells D4 and E4 contain the first of the month and last of the month respectively.

I would like to avoid the easy answer, start a new workbook each month, but I won't be the person entering the data or using the separate sheet to conduct performance reviews so it needs to be one workbook that lasts from month to month.

View 1 Replies View Related

Transpose Multiple Columns To Rows Based On Criteria

Sep 26, 2008

I am stumped on how to transpose multiple columns to rows based on specific criteria. Here is an example of the data I am working with:

Acct #Rev CodeUnitsCharges10094537034503$0.0010094537034501$605.0010094537037101$0.0010096359034503$0.0010096359034501$355.0010096359037101$0.00

I want it to look like the following:

Acct #Rev CodeUnitsChargesRev CodeUnitsChargesRev CodeUnitsCharges10094537034503$0.004501$605.007101$0.0010096359034503$0.004501$355.007101$0.00

I should note that there is oftentimes more than three rows for the same account number, sometimes it could be as many as 20 rows for the same account.

View 11 Replies View Related

Combining Some Of Rows Values Based On Multiple Criteria (2)

Apr 18, 2014

I have a table based on transport numbers who sometimes have doubles. Example:

Number Postcode Value1 Value 2...
106200 8500 10
106200 8500 5
106200 8600 6
106201 5500 4
106202 4000 1
106202 4000 1

So it works as following:

A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine

So shortly:
Transportnumber double/triple...+ Postcode double/triple...= combine these rows.

if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.

So example of end result for 106200 would be
106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to)
106200 8600 value ( this was unique so it stays unique)

View 4 Replies View Related

Reverse VLookup (Index Match) To Return Multiple Values Based On Single Lookup Criteria

Jul 11, 2012

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.

View 1 Replies View Related

Finding Duplicate Rows Based On Values In Multiple Columns?

Mar 28, 2014

I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. I basically want to Group based on 4 fields and find the average of the 5th field.

My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.

View 9 Replies View Related

Summing Values From Multiple Worksheets Based On Rows And Columns

May 9, 2008

I have Master sheet where I collect info from sub sheets. All sheets are similarly formatted, ie. product numbers on column A and headers on row 2. I need to sum values from all sheets based product number and header. Master sheet includes all product numbers and some extra headers, sub sheets include only needed numbers. Headers on sub sheets are identical.

Currently I have this thing solved with following formula:

N48=sumproduct(sumif(indirect("'"&$B$378:$B$385&"'!A:A");A48;indirect("'"&$B$378:$B$385&"'!L:L")))

Where B378:B385 includes sheet names.

But problem with this is that column is hard coded, so I have to know that that value I am looking for is in column L. That wouldn't be show stopping problem on its own, but I have columns all the way to DR and copying formulas for each column takes a lot of time when I have to manually update each column. Just copying cell holds that L:L and doesn't change it.

So, in addition of getting values for specific product number I need to get values from specific column based on column header.

View 9 Replies View Related

Return Max Value Considering Criteria In Multiple Columns

Jan 10, 2014

I would like to return the value in column D (Store Name) that corresponds to the Max value in column N (Units Still Required). However, this Max value must meet certain criteria. That is, the State (column J) and Style Code (column Q) must be the same as that of the row being considered.

I have tried the below formula, and it appears to work the majority of the time, however, occassionally it does not adhere to the criteria (i.e. same State and Style Code).

For example in cell M7:
=IF(L7=0," ",INDEX(D$7:D$999,MATCH(MAX((IF((J:J=J7)*(Q:Q=Q7),N:N))),N$7:N$999,0)))
CTRL + SHIFT + ENTER

View 2 Replies View Related

Return Multiple Results In Multiple Cells Based On Criteria In Yet Another Cell?

Feb 1, 2012

Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)

e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated

Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.

e.g.
AAA blue round elongated
BBB orange
CCC smelly

View 3 Replies View Related

Match Two Criteria And Return Multiple Rows

Oct 26, 2011

I'd like to return multiple rows with two matches on criteria (ID and First Term). I'm able to return all grades for a given ID but do not understand how to get the second criteria, First Term, in my statement.

=INDEX($F$2:$F$12, SMALL(IF($A16=$G$2:$G$12, ROW($G$2:$G$12)-MIN(ROW($G$2:$G$12))+1, ""), COLUMN(A1)))

Code reflects the ALL GRADES data below.

TERM SUBJECT NUMBER CRN CREDITS GRADE ID
1 Biology 301 12345 4 B 123456
2 Biology 302 23456 4 B 123456
3 Biology 303 34567 4 A 123456
3 Biology 338 65432 4 C+ 234567
3 Biology 338 54321 4 A 345678
1 Biology 336 43210 5 B+ 456789
1 Biology 337 43210 2 A 456789
1 Biology 338 65432 4 B+ 456789
1 Biology 301 12345 4 A 567890
2 Biology 302 23456 4 A 567890
3 Biology 303 34567 4 A 567890

Row Labels Min of TERM Count of GRADE First grade All grades
123456 1 3 B B B A #NUM! 234567 3 1 C+ C+ #NUM!

345678 3 1 A A #NUM!

456789 1 3 B+ B+ A B+ #NUM! 567890 1 3 A A A A #NUM! Grand Total 1 11

* Need all grades where TERM = Min of Term

View 7 Replies View Related

Return Multiple Values Using Three Search Criteria?

Jun 9, 2014

I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F$9 (see below) is giving me an error

=IF(ROWS(A$9:A9)>$A$5,"",INDEX(Data!A$2:A$387,SMALL(IF((Data!$A$2:$A$320>=$A$2)*(Data!$A$2:$A$320<=$B$2)*(Data!$B$2:$B$320=$C$2),ROW(Data!$A$2:$A$334)-ROW(Data!$A$2)+1),ROWS(A$9:A9))))

test1.xlsx

View 3 Replies View Related

Return Column Values Based On Criteria?

Jan 31, 2014

I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45

The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.

I need a formula that would look at my source data, and populate a sheet in the following layout

agent_id, agent_name, title, start_time, end_time

The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30

If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.

View 2 Replies View Related

Return List Based On Multiple Criteria

Mar 13, 2014

I have a spreadsheet with several hundred line items that I basically want a summary of.

STRINGS CRITERIA OTHER
string1 criteria1 other2 <---disregard because of other2
string1 criteria2 other1
string1 criteria2 other1 <---disregard because it is a duplicate
string2 criteria3 other1
string2 criteria3 other1 <---disregard because it is a duplicate
string2 criteria2 other2 <---disregard because of other2
string3 criteria1 other1
string3 criteria1 other1 <---disregard because it is a duplicate

I need the formula to return the value in STRINGS & CRITERIA, but I do not want it listing duplicates.

-I need the summary list on a separate sheet
-STRINGS, CRITERIA, and OTHER are all strings
-STRINGS are in alphabetic order
-CRITERIA are listed in groups with regard to their STRING, but not necessarily alpha-order
-OTHER are not in any particular order, but there are only two choices for OTHER
-OTHER: if OTHER=other2, it should not be listed in the results

RESULT

STRINGS CRITERIA
string1 criteria2
string2 criteria3
string3 criteria1

View 1 Replies View Related

Return Result Based On Multiple Criteria

Sep 19, 2009

I have been trying this for hours but to no avail.

I have a table with 4 columns headers
Name, Amount, Loc and Code

The name may look like ABC 1, ABC 2....
The Loc may be in US, GB...
and the Code may be AA, BB

I need to return a result "Y" if the sum of the amount is > 100
and "N" if the sum of the amount < 100 based on the conditions
of the following :if

1) Name is the same entity, such as ABC 1 and ABC 2 and
2) Loc is the same, US..and
3) Code is the same

I have attached a sample to illustrates the result

View 10 Replies View Related

Return Multiple Results Based On Criteria

Dec 1, 2006

Please see the attached sheets.

Here's the issue:

On sheet "master" I have a list of goals in column A.

Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.

I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.

I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.

Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.

how this done for AL, I can go ahead and create the other sheets.

View 9 Replies View Related

Copy Rows Meeting Multiple Criteria On Multiple Columns

Dec 18, 2006

1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet

View 9 Replies View Related

Query Worksheet & Return Rows Based On Conditions/Criteria

Jun 1, 2008

I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.

1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.

2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.

View 4 Replies View Related

Return Multiple Values That Match Single Search Criteria?

Nov 28, 2012

Attached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.

I would like to be able to get a list of all values in a given column that match a specific name in another column.

Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.

For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.

The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.

CPT Sample Book.xlsx

View 4 Replies View Related

Populating Fields In Multiple Cells Based On Criteria In Multiple Columns?

Feb 24, 2014

Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.

In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.

CURRENT SHEET

ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....

The attached example file may be a better alternative to view this problem.

View 2 Replies View Related

Search Text String And Return Value Based On Multiple Criteria?

Apr 8, 2013

Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?

E3a7qta.png

View 1 Replies View Related

Count Rows Meeting Multiple Criteria Of Multiple Values

Aug 10, 2009

I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.

I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.

I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.

View 2 Replies View Related

Search Workbook For Cells That Meet Multiple Criteria And Return Results From Rows

Jun 28, 2013

I would like to perform a search on only the worksheets listed in a worksheet titled table of contents. I would like to use multiple criteria for this search and send only the unique results to a worksheet titled results. Each worksheet listed in the table of contents has a cell address for each heading that I would like to extract data from the same column. The attached workbook example shows the data that I would like to collect when I search for cells that begin with "AB" and cells that begin with "CD". I collected this data by copy and pasting all the data from each worksheet into the results page and then applying filters and advanced filter to remove duplicates. This method does not work well for the original workbook as the data is quite extensive.

Create_List.xlsx

View 4 Replies View Related

Averaging Multiple Columns / Rows If Two Criteria Are Met

Apr 30, 2014

I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.

I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).

Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.

Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.

The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?

Location
Parameter
2008
2009
2010
2011
2012
2013

LocA
alpha
0.24
0.84
0.47
0.27
0.14
0.33

LocA
beta
4
9
9
8
2
9

LocB
alpha
0.24
0.33
0.85
0.54
0.56
0.65

LocB
beta
8
7
6
7
2
9

LocC
alpha
0.24
0.36
0.65
0.66
0.85
0.92

View 3 Replies View Related

Sumproduct For Multiple Criteria Across Columns And Rows

Jan 10, 2014

I've not used SUMPRODUCT previously and can't understand how to get results for the attached.

I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.

I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.

The second table is actually in a different sheet but is the source of the data I need added.

Sumproduct P&L.xlsx‎

View 11 Replies View Related

Sum Range: Multiple Criteria In Rows & Columns

Oct 20, 2006

I posted this on Mr Excel, but did not get an answer. I hope someone here can help.

I have a rather large financial worksheet that I am trying to extract some specific data from.

The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column.

Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month.......

With me so far?


What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year.

In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up.

Just in case that is not totaly clear----

criteria to meet

Column G = "Project Code"
Column J = "Exp"
Row 2 > this month
Row 3 = Forecast.......................

View 9 Replies View Related

Compare Values In Multiple Columns And Return A Result

Mar 17, 2009

I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.

I have a column between the years that calculates the percent of gross revenue for the specific department/cost.

I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.

The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.

View 7 Replies View Related

Return Values Using Lookup Value From One Sheet Across Multiple Columns

Dec 11, 2012

I'm trying to find a way to:

Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"

Things to note:

a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.

b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".

If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".

If that value does not occur in sheet B, the corresponding cells should remain blank.

If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.

View 2 Replies View Related

Multiply Multiple Columns Based On Multiple Criteria

Jan 23, 2012

So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...

1.) Take their associated % (Columns C, E, G)
2.) Multiply the % by the Revenue (Column H)
3.) Sum up the total for each of the names (Locatedin Columns B, D, F)
4.) Only sum up the ones that occur in Month 1 of the Year 2012

I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..

Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00

View 3 Replies View Related

Find A Value Based On Criteria In Columns And Rows

Dec 5, 2008

I have a workbook in which I have two sheets. One sheet is a report and the other is a data dump. The data dump has headers in in column A starting in cell A6 and headers in row 5 starting in cell B5. There is then data going from B6:J20.

In my report I then I have same setup with headers in column A and row 5. The difference is that the headers are not in the same order as the dump. What formula could I use that would look for the two headers in my report sheet and then match it with the value in the data dump that uses the same two headers?

View 2 Replies View Related

Insert One Or Two Rows Based On Two Columns Criteria

Feb 3, 2009

I need to insert one or two rows depending on the criteria of two different columns.
We have two shops (A and B)...and the sales are expresed like this: ....

View 9 Replies View Related

Multiple Options Cell Values Based On Multiple Criteria Variable Inputs?

Mar 18, 2013

I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.

I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).

The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.

As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.

The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R

You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.

This is the area where my skill at using scripting dictionaries fails.

The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words

if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")

So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)

Here is my code so far.

VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")

[Code].....

View 1 Replies View Related

Sum Multiple Criteria In Multiple Columns And Rows

Jul 10, 2013

create a formula,for multiple criteria with multiple results in rows.

[URL]

View 1 Replies View Related

Auto-Populating Multiple Values Based On Two Criteria Based In Comboboxes

Aug 7, 2013

I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.

View 2 Replies View Related

Delete Rows Where 2 Columns Meet Multiple Criteria

Aug 30, 2007

I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.

Please see the attached, it is more clear I hope. The two yellow rows would be deleted.
The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"

View 8 Replies View Related

Delete Rows Where Multiple Columns Meet Criteria

Apr 4, 2008

I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.

The objective is to:

To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.

TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.

View 3 Replies View Related

Extract Values From One Column Based On Two Criteria From Two Other Columns?

Feb 14, 2013

In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?

I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.

View 1 Replies View Related

Sum With Multiple Criteria A Number Of Rows And Columns From Original Data

Sep 22, 2006

I have 6 columns of data laid out as follows,

columnA columnB columnC columnD columnE columnF
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting

= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))

View 9 Replies View Related

Return Value Based On Multiple Possible Values In A Cell

Nov 21, 2007

I have in A1 a string of 100 characters..

I would like to create a SELECT CASE where,
if in A1 there is the word "BLACK" , put 1 in A10
if in A1 there is the word "WHITE", put 2 in A10
else put 50 in A10

I'm trying to use the instruction INSTR but with negative results..

(I have many cases other than BLACK and WHITE, that's why I need a SELECT CASE)

View 4 Replies View Related

Add Multiple Values Based On Criteria?

Jul 16, 2014

Add Multiple Values Based on Criteria

Search for the word "Clear" in the Task column. If the word "Clear" is found, go to Value column. Add all values starting from "Clear" down to the end of the list. If it is larger than 10, return the word "OK". Otherwise "Poor". If larger than 20, return the word "Good". The length of the list is variable.

Here is the sample worksheet (Please click).

View 7 Replies View Related

ADVERTISEMENT