Three Criteria Irregular Lookups
Sep 14, 2009Without using helper columns, what is the best way to return a value from an array using three criteria from irregular data? The attached sample gives and example.
View 10 Replies
ADVERTISEMENT
Lookups Matching 2 Criteria
Dec 23, 2009If 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.
Find Top X Percentages With Duplicate Criteria Lookups
Apr 3, 2009I have a matrix of suppliers which stores quality ratings against various service parameters. Customers then order their priorities and my intention is to then generate a report which lists the suppliers that meet their parameters.
a method of generating this report (on ClientRecommendation in the attached) which lists the top 5 suppliers under each weighted parameter, together with their scores? I realise that the answer must lie in some combination of INDEX and MATCH, but I cannot get my brain around how to generate the row numbers, as top 5 suppliers often share the same score for a particular parameter. For example, if I set up a row lookup on 80% I can so far only get one occurrence of 80% and no others.
This is probably not clear from my explanation, so I have attached the full example, from which it should be more obvious.
I have also seen a very clever VBA solution (#60695), which I think may be the neatest method, but I am not knowledgeable enough to make it work for my case.
MID Function For Irregular Pattern
Sep 9, 2013I'm trying to extract specific character from a set of text strings which have irregular patterns. For example:
CJWHCon.D005 - JN Holding Inc Corporate
CUSViWind.D404 - Windows Ringtown Vinyl
My goal is to get only the 'DXXX' which sometimes might be only 'DXX'. I've tried using the following formula but I seem to be going wrong somewhere as I only get 'D'.
=MID(F10,FIND(".",F10)+1,FIND(" ",F10,FIND(" ",F10)+1)-1-FIND(" ",F10))
Searching Irregular Strings
May 1, 2007I am trying to parse stock holdings for certain companies but I am running into trouble because they are not uniform in their form. The data has a general form but each company uses a slightly different style for the forms. These differences are hamstringing my efforts to effectively parse the data. I have attempted to parse the data using the split function to get it into a more manageable form, and then use various comparisons to tease out the right information. This, however, has not panned out like I hoped. Below are some sample data forms and some of my (albeit poorly written and structured) VBA to try and solve the problem.........
I am trying to extract the same fields from each, such as name or number of shares:
3COM CORP 885535104 1,700,000
Prudential Financial, Inc. 432848109 100,000
this would be a desired output.
here is some of my code to try and accomplish this. this seems to work the data if it is formatted correctly, but I would like write a general parser, and this is what I am struggling with. I cannot figure out how to write something that will correclty parse these irregular strings.
txt = ActiveCell.Value
x = Split(txt, " ")
name = ""
cusip = ""
shrs = "".....................
Inserting Irregular Number Of Rows
Jan 22, 2013I need to write a macro which inserts rows such that each ID has exactly 10 rows (1 for each brand). The difficulty is that each ID has a varying number of blank rows required.
ID
brand
10
1
10
3
[Code] ..........
Sorting Irregular Data From CSV File?
Jul 18, 2013im looking for a way to sort some data exported from an old stock recording software. the system outputs the data as a .csv file but each item bought is grouped into a particular heading, a line is only generated if money is spent under that particular heading. i cannot make the software generate lines if no money is spent so the number of lines generated each period changes.
period 1
3202 fuel £3000
3203 PPE £250
3204 major units £5000
3205 planned maint £212
3207 unplanned maint £457
period 2
3202 fuel £2500
3203 PPE £120
3204 major units £1000
3208 storage £212
3210 windows £457
if the data was regular i could simply add the totals each month and divide to get the average.
what i am looking for is a way to auto sort the data onto lines by possibly injecting the missing headings with zero as the total spent.
How To Get A Chart To Work With Irregular Dates Correctly
Jan 3, 2014I've tried for some time now to create a chart where the x-axis make up for irregular dates. Making the line between 2 points longer if it's long time between them and short if it's short time between them.
I've finally understood line charts doesn't support that and I have to use scatter graph. It seems to work but the dates, the get all messed up. I type 2014-11-11 but the x-axis in the graph shows 2283-12-12 or something like that.
Got a notion that I might have to transform it to the date value manually first but I get #VALUE ERROR
i.e. 2014-11-11 in A1, then I type in A2 =DATEVALUE(A1).
How do I get a chart to work with irregular dates correctly?
Identify Non-blank Cells In Irregular List
Jun 21, 2007I regularly import a list of data into Excel containing employee team names, employee names, and (for these purposes, irrelevant) data. The team name is the only data in column I, and therefore I can easily pick this up an summarise it in a lookup. The employee name is always one row above it, and in column D. However, it is not the only data in column D, and each employee's records contain varying numbers of rows. How can I have Excel copy the employee name in to column J, alongside the team name in column I, but ONLY in rows where there is data in column I?
View 3 Replies View RelatedArray Copying And Pasting Columns Following Irregular Distance?
Jun 21, 2014I am trying to copy several columns into another sheet within the same workbook.
I want to copy Columns 1,2, 15, 19, 23, 40.
I don't know how to work with arrays.
Text To Columns Macro For Irregular Spacing Of Data
Mar 1, 2010i have written a macro to parse data in to four columns using the text to columns fixed width option, but unfortunately the data i get changes its spacing and configuration every day, meaning that the fixedwidth columns dont separate the data correctly. below is how it looks some of the time.
46632hac5 Jpmcc 2007-ld12 a5 19,340,005 315
0738qac5 bscms 2007-pw17 a5 23,142,005 265
61746wcz5 msdwc 2000-prin a5 3,600,005 305
32108hp75 bacm 2007-2 a5 2,000,005 465
46630edf5 lbubs 2006-c1 a5 1,000,005 285...............
Auto Sum With Irregular Rows With Existing Formula - Bold It And Draw Lines
Feb 28, 2014I have a file with many different row sizes and after calculating the figure, we will need to add up the total for a particular job. Due to the irregular data on each job (some with only 1 line item where sometimes the job have few line items).
The cell to sum already has a formula for the data.
For example, I already have a formula on Column L to get the figures. After few line items, I will need to add a total to the lines item, also on Column L. I think we can uses 2 sets of data from 2 different column as "pointers", Column D and the formula column itself (but I don't know how to). The columns that I need a sum function will be from Column L to X. After doing the sum, can we at the same time draw a sum line? (single top line and double bottom line).
Am I being "too greedy" ? Imagine if we have more than thousands lines of such data to be added manually and to draw these lines manually.
Sum Of Lookups
Mar 12, 2007How can I conditionally total based on a Lookup?
e.g.
Column A shows the item code.
Column B shows the age of stock items as 1, 2 or 3 (years).
Column C has the cost of the stock items.
I want to total the costs for stock items (column C) according to age, so that the totals at the bottom would look something like:
Total stock value for year 1: $14,537.10
Total stock value for year 2: $7,657.70
Total stock value for year 3: $1,773.00
Lookups But Cut Out All The Numbers
Apr 4, 2014How could I do a lookup on the following accounts but I want to ignore all the numbers and spaces. For example, in another sheet, I just have ACCRUED PTO and I want to do a vlookup on 601190 601190 5007 ACCRUED PTO.
407200 3044 UPR-STAT SELLER COV F.R.
407300 3047 UPR-GP/ST SELLER COV F.R.
407200 3040 UPR-STAT BUYERS
601190 601190 5007 ACCRUED PTO
601150 601150 5033 WAGES OVERTIME
Using Lookups & IF Queries Together?
Dec 28, 2012I'm currently working on a side project for work that I believe will save many man hours as at the moment we're using paper and calculators!!! I'm scheduling buys. I've worked out formulas and formats to show how much we should buy and how they should be split (for example for UK purchases we buy x amount and split it 10% 90%). However, the dates of delivery are different. See below:
PORT L'poolPORT HKPORT Portsmouth
1.113-Aug-1206-Aug-1230-Jul-12
1.210-Sep-1203-Sep-1227-Aug-12
2.103-Sep-1227-Aug-1220-Aug-12
2.201-Oct-1224-Sep-1217-Sep-12
3.124-Sep-1217-Sep-1210-Sep-12
3.222-Oct-1215-Oct-1208-Oct-12
Campaign 1 first buy (1.1) I know that my product is sailing from port HK. Therefore I want it that in the 'delivery date' cell, it returns 6/aug/12 (that's the first lot of 10%, and underneath it returns 3/sep/12) which the remaining 90%...2.2).
However, both the campaign number AND port options are variable (using a data validation drop down list). In effect meaning if I choose campaign 2 instead of campaign one, it'll return the dates 27/aug and 24 sep....if I then change the port to Portsmouth, the dates will be 20 aug and 17 sep.
I've attached a quick view of the schedule I've created. The yellow cells are where I want the dates to be returned.
ScheduleEX.jpg
Lookups In Matricies
Mar 3, 2007I'm trying to use a state transition matrix in Excel. I randomly generate a
percentage and want to lookup the value in a corresponding table where this value would fall if we were to sum across the row. For example, the first randomly generated value is 13.5% and we are starting in state 6. Therefore, I want a formula that looks in row 6 (starting state) and determines which column (1-6) has the value 13.5% if you sum the values across (in this case the answer is 5). Then 5 becomes the new starting state and so on. Is there a formula or combination of formulas in Excel that can carry out this complex function?
State transition matrix
123456
185.00%10.50%3.10%0.74%0.66%0.00%
28.90%79.00%9.20%1.70%1.20%0.00%
31.00%7.50%73.00%15.00%2.50%1.00%
40.00%1.00%13.00%76.00%9.00%1.00%
50.00%2.00%5.00%6.00%71.00%16.00%
60.00%1.00%1.00%6.00%9.75%82.00%
Sum Using Lookups And Match
May 22, 2007I have a master table that has a list of Product Names in column A and the associated product rollup names in column B.
Next, I have a data table that has a list of company names, Product Names (the names are all included in the master list of Product Names), Transaction Type (Sale, Cancel) and Value.
Lastly, I have a data summary template that I built where I want to be able to calculate total values based on the data table and the product rollup from the master table.
I would like to build formulas into the summary template where it looks at the Product Name column from the data table and finds the associated Product Rollup from the master table and then sums all the values for Transaction Type Sale and then sums up all the values for Transaction Type Cancel. The end result would be a Sales Value for Product Rollup 1, a Sales Value for Product Rollup 2, a Cancel Value for Product Rollup 1, a Cancel Value for Product Rollup 2, etc.
Check Lookups
Jan 28, 2008I have 2 sheets, First is the Master that contains 2 columns (Item Code and Item Name), The second also contains the transactions for item codes and item names.
I can check all the item names that it's entered according its code using Vlookup for every row...My question: Is there an array formula (one formula) to check that all Item names is correct according to its code in Sheet1?
Conditional If's And Lookups
May 3, 2008Here what I want to do.
I have 2 columns A and C.
The A column displays the winning teams every week and column C displays the losing teams each week.
our league has 2 divisions. Now during the regular season schedule we go head to head against our own division and against the other division.
What I would like to do is keep track of win loss record against each division and against the others.
So for exampls if the Cardianls win against the Angels the Cardinals would be in column A and the Angels in column C. The Angels are in our division. (So are the Braves, White Sox, A's). And if the Cardinals beat the Yankees, the Cardinals are again in the A column and the Yankees are in the C column. In the other division are Blue Jays, Mets, Red Sox and Cubs. From that I would like to see the result of NL 1-0 and AL 1-0.
Let me know if you need more info.
COUNTIF, LOOKUPS And ROW
May 7, 2008Sheet 1 has a data entry sheet - with a list of Local Authorities down the left, and criteria against which they are scored along the top. They either score, 1, 2, 0, or 'Unknown.' The order may be changed through sorting.
Sheet 2 is a summary, and I need to count how many 'unknowns' there are for each line.
I can't figure it out. And I am sure it is dead easy. In my defense I have been in bed ill for a week, and my brain isn't firing on all cylinders.
Sumproduct And Lookups
Feb 21, 2007I need a formula that combines sumproduct and vlookup. Basically i have a speadsheet with staff names and hours worked in two columns. I need to lookup the staff names to return their cost rates and multiply it by the hours for each individual. However, there may be 20 members of staff so don't want to have to do a formula for each person.
View 3 Replies View RelatedLookups Between Sheets
May 15, 2007I have a player list, from which i have put a sample below:
ALL ROUNDERS W1 W2 W3 W4
Garry Adamson ar 23 2 45 12
Charlie Baxter ar 10 0 23 14
Iqtiwar Chaudray ar 0 0 1 2
Hugh* Doulton ar 0 5 12 10
On the left is the player name and then to the right are their scores for the first 4 weeks. Each team that has entered the league has a tab of its own with the 11 players they have selected on. I would like to just have to update the main sheet and then have a look up in the other tabs on an if the player is ion this squad basis... I have attached an example of the whole player list and an example of a team on the 2nd tab.
Lookups With Multiple Inputs
Aug 19, 2009I have a spreadsheet with a medium-sized data set, and I need to look up values from the large table, and thin the table down to only the relevant values.
To arrive at each unique value, I will have to match 3 input cells to the first 3 cells of the data table, then copy values from across that specific line to the appropriate cells of my smaller table. I really don't need to sum or do any math in this step, its just a copy from the master table to the smaller table, the math will come later.
I think I'm attaching the file (remember-first post) so hopefully it will help with what I'm trying to accomplish. Hopefully this isn't a big deal, but I've been pulling my hair out on this all day and I just can't find a solution anywhere that really matches up to this.
Get A Count From Multiple Lookups
Jul 11, 2006On sheet1 I have a column with multiple names. I need a function that can
look up all those names from column B of sheet2, and then count the occurance
of a value in column H of sheet2. Here's the example:
Sheet1 (note that some rows are blank, and some names may appear >1 time):
Helen Back
Cyndi Lou Who
Cyndi Lou Who
Sam Spade
Sheet2 (names only appear once):
col Bcol H
Cyndi Lou WhoR2
Dan DangerR1
Dou LuvaR3
Hal BrookR2
Helen BackR1
Joe MamaR2
Lou DuvaR2
Mai TaiR2
Roberta FlackR3
Ron PopeilR2
Sam SpadeR2
Will NotR2
Yodan/a
I need a formula that can do lookup sheet1's names in col B of sheet2,
then give me a count of all values "<>R1" in col H. If names are repeated in
sheet1, I do want to include the multiple occurences in my count.
Set-up Spreadsheet For Lookups & Reporting
Aug 19, 2008I run a store, and currently we handwrite all of our reciepts - and then implement them into a "Sales Sheet" on Microsoft Excel. From there we can monitor monthly sales and targets - coincide what we are selling, and figure sellthroughs etc etc.
To make life easier I am looking at typing out reciepts in Excel - within the same workbook as the "Sales Sheet". I would save each reciept as a separate sheet, and would like to know if it is possible to somehow copy data from the "Reciept" worksheet to the "Sales Sheet" worksheet??
One Input, Two Lookups, Two Reports
Nov 13, 2006Some notes on my example file:
The data would get pasted into the first sheet, in the shaded area. It might be exactly those IDs, or others - possibly a longer or shorter list each week.
Sheet 2 has the lookup master lists, or checklists. I can set these up ahead of time, and don't change week to week. The input IDs will be checked against these lists somehow on the Report pages.
Sheet 3 and Sheet 4 are the Report pages.
I want a formula in column A to match the input ID values from Sheet 1 to their appropriate checklist on Sheet 2, and return the ID value on the appropriate Report. If the value matches Checklist 1, it should repoirt on Report 1. If it matches Checklist 2, it should report on Report 2. If the value isn't on the checklist, it shouldn't report, don't report it. If there are ID values on the checklist that don't appear on the input, they also should not report.
On the Report pages, once I have the correct IDs reporting, I can put in other formulas to pull Revenue, Cost, etc. info from elsewhere, VLOOKUP-ed from the IDs. (I know how to do that.)
The key is that in terms of sorting out the correct ID values to the correct reports, I don't want to have to do anything to the Report pages each week (like sorting to get rid of blanks, etc.) - I just want to paste in the Input.
Sum By Date Comparisons Or Lookups
Jun 25, 2008I have monthly revenue information going across columns identified by an account number on sheet 2. On sheet 1, I have an order date alongside the account number. I need a formula that will return total revenues (on sheet 2) for all that occured before the order date, and all that occured after the order date.
I have attached an example here...the two yellow boxes are where I am looking to place the formulas on sheet 1.
Lookups That Retrieve Portions Of Text
Aug 20, 2014If you look at the workbook I have attached, I have made a mock up of a file that I have to use. Cells A1:F12 are part a "Situation Status" file. My team uses this file to update that status of certain events. It shows the current planned estimated completion date for each event in each area. We are required to strikethrough every time the estimated completion date slides out. In some cases, the dates have move 3 or more times so there are cells with multiple dates with strikethroughs. The current date is always the last date in the text string and does not have a strikethrough.
Cells H1:J17 are part of a matrix that I am making to try and capture only the most current ECDs. I want I3 to show "8/10" instead of "8/1 8/3 8/10."
The Event Status and Event Matrix are part of two separate files that I have. The Event Status file is constantly changing so I can't simply say =RIGHT(B4,4) because I could wind up showing the date of a different situation.
Is there a way to lookup the situation name (i.e. "Situation 1"), the Area number, and the event number and return ONLY the latest ECD (the date without the strikethrouh)?
Multiple Lookups In Single Cell
Jun 13, 2014I am looking for creating a dynamic meeting log/calendar for my team. There's a couple of elements to my questions below:
I have a log with columns such as "Meeting Date", "Client", "Attendee" and have also set up month calendars on separate tabs which automatically update the dates according to a "Year" toggle using Janoffset, Feboffset, etc.
What I would like to do is have these calendars automatically populate a cell beneath each date with any meetings on that day. The client name and then a space then the attendee in brackets would be sufficient and with each meeting having its own line in the cell).
I have gotten a Vlookup working but have run into two snags: 1) where there is more than one meeting on a single day 2) I can only return one piece of information in the cell or otherwise have it blank, I can't have the client name AND attendee.
Is there any way to lookup all of that information in the one cell or is this just a pipe dream?
I have attached my workbook which contains my workings so far and in the first cell of Jan shown the "ideal" format. I'd like the data in.
Multi Lookups (VLookup To Report)
Jan 14, 2009Can you get a VLookup to report the first value it encounters but then continue and report all other instances.
I am only aware Vlookup searches a range until it finds the first instance.
e.g.
A Apples
B Pears
C Grapes
D Oranges
A Insects
C Animals
A Bugs
B Blue
A vlookup of "A" would return Apples and I want it to return in column 1 Apples column 2 Insects and column 3 bugs.
The data is all mixed up and cannot be sorted so I could sort into blocks to change the lookup range.