I have code that runs through multiple arrays trying to match data to items in the arrays and it takes a long time to run.
Code:
dim a as long
dim b as long
dim c as long
dim d as long
dim e as long
dim MyAarray as variant
[Code] ..........
That's basically what the code does. however, it takes an extremely long time to get through with everything as each array increases in size.
I have attached a sample from aspeadsheet I am working on. The option reply sheet has '1' entered if the student has opted to take this course. On the other subject sheets I would like the names who have opted for that subject to appear. Stuidents can opted for multiple subjects. I have tried Match and Index functions but it is difficult as students can opt for more than one.
View 1 Replies View RelatedI'm working on a travel form, which allows people to enter a travel itinerary. From that, I want to be able to extract the cities where they are spending one or more nights - so that I can then do the calculations for accommodation allowances.
I am able to calculate the number of nights stay in each city where there is an overnight stop. But I'm stuck on how to extract every combination of City and Nights where Nights is greater than zero - there is no need to calculate for cities with no overnight stay.
I have attached an example spreadsheet.
Some notes:
- the itinerary and accommodation tables have to remain separate, as they hold more data than in the example,
- the itinerary table can't be sorted for number of nights. It has to be in chronological order for each segment!
Every lookup function I check out seems to demand a sorted table.
I have two worksheets: A and B.
Worksheet A:Contains 2 columns: Issue# (Col. A) and Program (Col. B).
Issue# contains a list of multiple issues. There are several instances in which the issue# is repeated.
Any particular Issue# field may have several issues in it, delimited by a comma.
Program is a program associated with the issue and this column also contains duplicates.
Worksheet B:Contains 1 column: Issue# (Col. A)
This is a unique list of issues#'s.
All Im looking to do is parse all Issue#'s from Worksheet B and have some way of knowing if that issue# is anywhere in Worksheet A. Most importantly, I need the "indicator" to be displayed on Worksheet A. This way I can see what program(s) is/are associated with the matching issues.
A couple other notes:All Issue#'s in Worksheet B are referenced at least once in the Worksheet A Issue# list
There are several issues in Worksheet A which are not referenced in the issue list on worksheet B (of which I dont care about)
I really hope that makes sense, but if not...
Here's the best example I could come up with: ....
I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
in my spreadsheet, there are two pages:
[data] ....
on the 11th row there should be a formula which sums corresponding column, according to how many items and which items were used in this particular set: corresponding prices should found from array "prices" (page a).
i'd like to use array formula (ctrl+shift+enter) if needed (like: {SUM((units=units2)*set2)}) or sumproduct function (like: SUMPRODUCT((units2=units)*set1) ), but can't use it, cause the arrays aren't equal. any ideas (except additional column on sheet B, where reside corresponding prices, then can use sumproduct function)?
getting correct formula and normally there are people much smarter then I in the
discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1)
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere
3 Mr. Someone 327 anywhere
Drver numbers are blank
On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers
numbers already filled in) I have this set up as a list.
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere 37
3 Mr. Someone 327 anywhere 39
What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it.
In Col A, from A1:A12 I have
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
In column G from A1:A6, I have
6
9
7
11
15
How could I return an 'x' in column C against the A values corresponding with the G values, for example an 'x' in C6, an 'x' in C9, an 'x' in c15' etc? And a blank for example at C8 and C14.
I think I've been at the Xmas sherry for far too long as I can't see the wood for the trees with the following code. It is supposed to find the position of the string_to_find within range_to_match.
I have checked and the address returned locally by range_to_match.address is correct. However the individual items (current_item) are coming back as blanks (as in the Msgbox) - rather than the members of the range.
I did try and do this via application.match but again got an error.
There is an option explicit at the very top of the module.
Code:
Public Function match_string(range_to_match As Range, string_to_find As String) As Long
Dim i As Long
Dim pos As Variant
[Code]....
I have two majors (classes) listed on two differents sheets and need to put the matching classes on a third sheet.
View 3 Replies View RelatedI am trying to have an order form auto populate information, however I have run into a small problem when doing so. I am unable to use VBA because it is locked by whoever originally created the spreadsheet so I need a formula solution if possible. I would like for the worksheet "Order Form" to auto populate the type of printer cartridge when there is a 0 in the column "On Hand" on the "List" worksheet. I know a loop could do the trick but I was wondering if maybe a vlookup or if statements could get the job done. Here is a completed sample of what I would like to happen.
View 4 Replies View RelatedI am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
I have created a DAta Validation List and it only select one item. I want it to select as many items that is on the list but i can't get it to work for my main worksheet I am working on. I can't figure out why I can't get it to work for the work sheet "This Don't" but it works for the worksheet "This Works" why and how I can change it so both Reason (Column F and H can) select mutliple items on there. Data Sheet.xlsx
View 2 Replies View RelatedI have a workbook with 4 sheets one is called final and the other 3 are data1, data2, and data3. The data sheets have the actual data I am needing to pull from. Each has two rows of data: data1 has employee number in column A and employee code in column B. Data2 has employee code in column A and employee name in column B. Data3 has employee name in column A and employee email in column B. Now I need to pull the info form all three sheets into the sheet names final. So the final sheet needs to contain 4 columns for employee number, employee code, employee name, and employee email. The kicker is the data sheets don't line up with each other within each sheet it does but the first one in data1 is not the first in data3 and so on.
View 2 Replies View RelatedI've got 2 worksheets SH1 & SH2( Two different workbooks) Col A is NAME in each Sheet. Need to Match Col A Sheet 2 to Col A Sh1 if they are identical. If identical copy info from Col B, and Col E- G to Sheet 1 Col B and Col H-J. If a Name is in Sh2 but not in Sh1, Copy (using same Column positions: copy info from Col B, and Col E- G to Sheet 1 Col B and Col H-J ) to NEXT AVAILABLE ROW in Sh1 COL A, and highlight it in Some bright color ; Then open a MSG Box telling me how many new names were added.
View 8 Replies View RelatedI watched a video on youtbe. And the women had three or four items in drop down box. She was using vba. When i emailed her to ask her how she did this, she kept telling me to go to her website and i was supose to figure it out on my own. I am trying to do a start up construction company. the link to her site is as follows Select Multiple Items from Excel Data Validation List - YouTube the idea of what i want to do, is how she clicks on a word and it follows in the coloumn to the right going down. This is what i need to know how to do. It is probably pretty elementary to many of you. But i build, i know how to read prints, to cut footprints for new homes etc. But when it comes to excel i am so green.
View 2 Replies View Relatedhello. i'm new to excel and i'm really hoping for some serious help here. i have the basics down however i'm really stumped at this point. Let me try to explain my worksheet. the worksheet has a sheet for data which is used for drop list values in the new patient template sheet. the new patient template which is just that a blank entry sheet that the user duplicates and adds a new patient to the worksheet to track the visits made by medical staff. there could be over a hundred new patient sheets (each named by the patient) at any given time.
here is what i need help with: my sheet is setup on a monthly basis so each patient has a total of 4 - 5 weeks listed with entries for everytime a nurse visits that patient. what i need to know is how to search the cell entries for the first time a visit occurred and the date it occurred and this needs to be broken down by the 1st of the month - the 15th and then again for the 16th - the end of month for every patient sheet in the workbook. the ranges are not together that i need to search for example f14:f20, i14:i20, f35:f41 and so on for the first 15 days of the month. this all needs to happen like in a macro or somehow automatically.
I have Workbook A populated with skus from a report and I want to remove all rows in this workbook that do not have a matching sku in Workbook B. Each workbook contains only one sheet and the skus are in column 1 of both workbooks.
Also, I am looking for a good resource/tutorial for working with workbooks, worksheets and ranges within. I don't anticipate this being the last time I will be confronted with this and would like to learn more.
I am trying to compare two colums. They both contain numbers mixed with letters. I am wanting to match only the numbers in both not the letters. Example:
column a = m454 column b = fsh454-1
m543 fst998-2
m998 fsm434-1
my match is m454 and fsh454-1, m998 and fst998-2. The items can be in any order in the column. The end result I want to indicate the match by putting an X by column a item that matches column b.
I need to find if there are matching addresses in 2 different excel files. If the same address appears in both files, I would like the new worksheet to return the address along with the sale price from the 1 file and the rental amount from the other file.
I have tried using vlookup but the problem is the exported data file contains the street number in one column and the street name on another column. I have attached a truncated example of both the rental data and the residential sales data.
excel filters.
I have excel document over 50 pages and i need to filter lines that begins with certain letters like ADS, SGH, FAQ. I know how to filter one by one but i need all 3 to filter in same time-
How can i make multiple lines to filter in one search?
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
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.
I have two column on two seperate worksheets. One worksheet has both columns filled out. On the second worksheet, I want to be able to type in info in once column and have excel pull the other column from the other worksheet.
WKSHT 1 WKSHT 2
ID Name ID Name
1 Bill 3 ?VLookup?
2 Steve
3 Mike
Ive tried using Vlookup('worksheet1'!A2:A4,'worksheet1'!B2:B4,2) but can not get it to work.
i want to do is import multiple text files,with fixed arrays (luckily it's standar )
I have the piece of code that i currently automatically importing these .txt files,although it's for one per turn.Here it is though,to see the arrays
what I'm looking at doing is counting the number of reccuring unauthorised absences in a list. We have the persons name in column B, the absence type in column C and the date in column E. Basically I need to send out an AWOL notice when 5 days of unauthorised absence for the person in column B has occurred, so I need some sort of indication that this has happened in order for me to stick some conditional formatting in there to flag it.
View 9 Replies View RelatedI'm trying to create a template that will be able to return a sales persons call target based on the category they sell and the current level they are. It works when I only ask it to look for one or the other, but I'm getting stuck trying to make it use both. I've attached a small sample. My original equation is as follows
=INDEX($C$2:$Q$51,MATCH($B2,$A$2:$A$51,0),MATCH(G$1,$C$1:$Q$1,0))
and this works perfectly fine. The problem I've now got is I need to add in a second criteria for setting the row_num. I've tried the following but I get a ref error. =INDEX($C$2:$Q$51,AND(MATCH($B3,$A$2:$A$51,0),MATCH(D3,$B$2:$B$51,0),MATCH(G$1,Telesales!$C$1:$Q$1,0)))
I have a procedure that I want to pass two arrays to. When I pass a single array it works fine but as soon as I try to pass two arrays I get a syntax error. Both arrays are declared exactly the same way and are used in the same way. Am I limited to passing only one array per procedure?
View 8 Replies View RelatedMy task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
I need creating a formula for the following situation.
I need to link multiple arrays from different sheets. But am having a hard time concocting a formula that will work.
I have 2 columns of data on sheet1. For example:
A-------------B
apples-----1245
oranges----456
nuts--------384
etc.
Now, I need a formula that will sum the entire column B, by looking up the array of column A on another sheet and only summing the numbers on sheet 1 that also have a value of "West" on sheet 2. Sheet 2 looks like this:
A-------------B
apples------West
oranges-----East
nuts---------West
This needs to be functional as a template to be used each month - the entire point of the formula is to save time and eliminate the current (and lengthy method that is in use). I can tweak the formulas as needed. But am trying to avoid having to concatenate anything or do any modifications to the format of the sheets.