Fill In Table Based On Multiple Criteria

Jul 11, 2007

I would like to fill in a table based on a small database
this table then creates a graph.

But I need to look at 3 criteria before I can fill in the table
namely: customer - weeknumber - weekday

based on these items the time should be filled in accordingly.

Can this be done with regular formula or should I try VBA ?

please have a look at my file the data should go from sheet input to sheet ACL
based on the above mentioned criteria

View 4 Replies


ADVERTISEMENT

How To Arrange Data Range Into New Table Based On Multiple Criteria

Aug 27, 2012

I have a problem whereby I have a list of data that has a date, a transaction and a balance. I want to be able to rearrange this data into multiple tables, one for each month.

The pictures below explain the situation better, on the left is the list of data I need to sort, and on the right is how I'd like the data displayed. So for the May columns I would like to display all transactions that happened in May and, depending on whether it is an income or an expense, the amount in the corresponding column.

i.e. so the finish result looks something like this:

View 9 Replies View Related

Expand Table To Include Missing Times From Data And Sort Based On Multiple Criteria

May 6, 2014

I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)

How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.

View 1 Replies View Related

How To Use Match And Multiple Criteria To Fill Multiple Cells

Nov 21, 2012

I am using this formula but I don't know how to get it to populate more than one cell.

Here is the formula:

=INDEX(APPROVED!$A$3:$A$1000,MATCH($F$4&$H$1,APPROVED!$D$3:$D$1000&APPROVED!$C$3:$C$1000,0))

It has multiple matches but I can only see the first match in the cell.

View 1 Replies View Related

Fill Table Based On Corresponding Table & Being Between 2 Dates

Aug 31, 2009

I have an 'existing results table' as per my attached sample. From this table I need to create a list of 'sold' for each annual date range - i.e. I need a separate list for items sold within that date range. see my example in red. *Note. I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.

View 4 Replies View Related

VBA To Fill In Cells Based On Criteria

Apr 4, 2009

I just stumbled onto this forum while Googling around, and it looks great. I'm pretty new to VBA coding so please bear with me. Effectively what I'm looking to do in this mock-up file, is anywhere the currency is "EUR" in column A, I'd like the relative cell in Column G to change to "INSERT TEXT", else I want it to untouched (i.e. to keep the comment it currently has).

View 6 Replies View Related

Fill List Based On Conditional Criteria

Apr 18, 2007

I have an Excel sheet with a column (column O) containing expiring dates. The dates represent the day a certain product expires, and for each date, a name is attached (in column A). So there are 500 dates in the sheet, each with a different corresponding name. How would you have Excel create a list, in a new sheet, of the names that are expiring in less than 90 days from today? That is to say, if the date in a cell in column O is less than 90 days from today, then the information in the aforementioned cell and the information in the cell in column A (in the same row) is COPIED (not moved) to a list in a new sheet?

View 2 Replies View Related

Fill League Table Based On Value Found Or Not

Apr 5, 2008

I am trying to update a league table from a different sheet. The table is not fixed on how many people with be in it as this can grow. This what I have in mind to do what I need but can work out how to implement it.

I need to loop though column "A" in sheet "U8" range A2:A?

The value in column "A" is unique so from "A2" I then take the value in "K2" for this unique id.

I now need to search column "A" in sheet "U8 League Table" for the unique ID place the value of "U8 - K2" into column "D" (Round2) row will be different in most cases to round1.

If the unique ID is not found in "U8 League Table" then add it.

The U8 sheet is populated a fresh per event (fortnightly) and the data fed (riders score will always be in column K, different row) into the League Table. However. as the rounds mount the data will need to go into next column along in the Table i.e Rd1 in C, Rd2 in D, Rd3 in E etc.

View 4 Replies View Related

Fill Table Based On An Drop-Down Choice In 1st Column

May 9, 2009

I'm working on making a spreadsheet that allows me to select an item type from a dropdown list (through validation) and then would copy in a range of values from another worksheet based on my dropdown selection.

The set-up: I am primarily concerned with two sheets in my workbook, BiS and Weights. I have a database of items and their associated values listed in Weights. I have already managed to get dropdown lists of my specific item types using named lists and have put that in the BiS worksheet.

What I would like to do is have the values in the Weights worksheet copied over in the same order and number of cells based on what item I select from the dropdown list.

View 6 Replies View Related

Fill Table Based On Matching Cell Values & Export

Dec 21, 2007

(refer to attached workbook)

For what I'd like to do, the attached workbook actually represents 2 or more workbooks. The 'Project-PO Report' tab is a sample of what will be several workbooks/reports that will be generated from the data in 'POTASK'.

The 'CMD_BUTTON' tab represents how I plan to use a command button " IMPORT DATA" to import the data into this workbook, then use "RUN PROJECT-PO-REPORT" to call the MACRO to achieve my objective,as stated below. (Originally,this workbook was about 8mb, so to get this under the size for attachment, I just copied the bare minimum of what I believe would be needed to assist me, and so there's no buttons or macros or dynamic name ranges, as there will be...)

Here's what I want to do: ...

View 3 Replies View Related

Select Row Based On Cell Criteria Remove All Formulas From Row And Change Fill Color

Apr 23, 2014

I have a worksheet that when a row changes based on the value of column B, I want to remove all of the formulas found in the row but keep the existing values, and then change the color of the row.

In the sample file attached, when the value is "Closed", that row will keep the existing values and then it gets grayed out. Rows that are still marked "Open" need to retain the formulas in case other information changes.

I have tried copy/paste special using autofiltering but that doesn't work because of the hidden lines. This file changes on a daily basis and I need a quick way to update the file.

SampleFile.xlsx

View 1 Replies View Related

Fill Multiple Cells Based On The Result In One Cell?

May 31, 2014

When I enter barcode X in cell A1, I want B1 to say supplier and C1 to say product name and D1 to say package quantaty etc.

And if I enter barcode Y in cell A2, I want B2 to say different supplier, and so on, hopefully you get the picture.

View 7 Replies View Related

Color Fill Multiple Rows Based On Condition

Mar 22, 2007

Is there a way to color fill an entire row based on a value in a certain column? Say I have a large file and one column is "yes" or "no" If I wanted all of the "yes" rows to be colored -

View 5 Replies View Related

Lookup Table Using Multiple Criteria

May 17, 2013

I have attached a demo spreadsheet to define the problem.

I wish to populate column K (Risk Rating) with data retrieved from the table, based on the corresponding information from Columns I & J.

e.g. I5 + J5 = Short term illness or injury + Unlikely. This corresponds to cell E9 (11) in the table so required response in K5 would be 11.

Help Book1.xlsx

View 1 Replies View Related

Fill Cells With Some Specific Texts Based On Multiple Conditions

Jun 18, 2014

I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.

Code to fill these blank cells automatically with some specific texts based on the attached conditions.

I have attached the workbook for more details. FillCellsTest.xlsm‎

View 10 Replies View Related

Find A Value In A Table Based On Other Criteria

May 30, 2009

I'm trying to find a suitable formula that looks at two cells (J3 and V3) in Sheet 1 (a person's employment grade (e.g. 18) and their performance percentage e.g. 92.5%), finds those values in a table on Sheet 2 and then places the corresponding value from that table in Sheet 1 at cell Z3.

View 2 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

Send Multiple Files Based On Criteria To Multiple Email Addresses?

Jun 29, 2014

I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc

I have a master that which will contain the branch in the first column.

I have defined the directory location in a separate sheet as well as email template.

When I click on the Send Email button I want ti to attach the files that match the Branch name.

The Branch list will constantly grow.

View 2 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

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

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

Summing Data From Multiple Workbooks Based On Multiple Criteria

May 14, 2014

I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.

Sample:

Location A Class:AA Segment: Sports Bar Rep: John Smith

Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1

I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.

I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.

View 2 Replies View Related

Search Table With Multiple Criteria And Return Row

Nov 10, 2009

I have been working on a variation of this for a long time and I cannot get it to work.

I am trying to depending on the textbox selection get the proper ROW from DEALLIST to show up.

View 2 Replies View Related

Populating Table With Data Using Multiple Criteria?

May 6, 2013

I am attempting to populate multiple rows in one column with data from another table. I need to get the correct street address using multiple values, i.e. first name, last name and city, as some of the names double up.

Is there a way to do this? I have pasted below an example of what I need done as reference.

Last Name
First Name
Address

[Code]....

View 4 Replies View Related

Extracting Data From A Table - Multiple Criteria

Jul 31, 2008

I have a data table that has the following structure


Team manager Warrington 10 12 14 16
Team manager Liverpool 8 10 11 12
Commercial Manager Warrington 25 28 32 33



I need to extract the data out into a flexbible summary table where i can choose the job title, location and year.

I can use the following index / match formula to extract the salary that matches the job title and location.

(INDEX('Emp In'!$c$5:$c$130,MATCH(1,('Emp In'!$A$5:$A$130=$B3)*('Emp In'!$b$5:$b$130=$C3),0) - Where b3 = job title, C3 = location, Column C = Salaries year 1

However I cannot add the flexibility to choose a year as this formula fixes the index on a chosen column (yr 1 column C is this instance)

View 9 Replies View Related

Pivot Table Criteria Based On Cell Value?

Jan 27, 2014

filter my pivot table using a cell value, rather than manually selecting from the dropdown.

The report filter title is Rnd H, and values start from 0 through to 2, at .1 intervals (so 0.1, 0.2, 0.3, 0.4 etc).

Rather than select "0.3" manually from the dropdown, can the pivot table do this if "0.3" was written in cell A1 for instance?

View 5 Replies View Related

Move Rows In A Table Based Upon Certain Criteria

Jan 3, 2014

I have four sheets with the same size tables and just need rows to be able to be moved back and forth based on input from a certain cell and just added to the next available blank cell in the desired table. Adding the code from the webpage below works very well but only puts into the worksheet and not into the actual table. Is there any way making it actually input the row into the table itself?

Creating Macro that automatically moves row to another spreadsheet?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "COMPLETE" Then
Target.EntireRow.Copy Destination:=Sheets("Archive"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub

View 1 Replies View Related

Create Unique Table Based On Criteria

Aug 31, 2007

Our reservation system exports a spreasheet daily which contains hundreds of records relating to bookings. The worksheet has an ID column which is not unique, but is sorted alphabetically. I need to write some code to delete all records below the first 'set' of records relating to the first ID value found. i.e. i want to end up with only the set of records relating to the first ID found. So the basic logic would be to look at the value of the field in the ID column of the first record and then iterate down the rows until it came to a different value and then delete all rows below that. Its simple in concept, but im stuck on how to write this.

View 9 Replies View Related

Multiple IFs To Display One Result Based On Multiple Criteria

Mar 18, 2014

I've got a matrix combining actions on the left hand side (Col A) and owners on Cols B to F. I have drop down list for each action/owner combo: N/A, OK, KO, TBD.

Owner #1
Owner #2
Owner #3
Owner #4
Owner #5
Results

Action #1
OK
TBD
N/A
N/A
N/A
OK

[code]....

I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...

View 4 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







Copyrights 2005-15 www.BigResource.com, All rights reserved