I have an excel file containing more than 70,000 lines (items and their corresponding orders) and in second file i have all the items listed. I want to find how using excel functions like vlookup i can return all the orders (from first file) against the items (in second file).
Attached a simple example of my problem. Please note that both tables are in different sheets of an file.
ITEMS
ORDERS
A
2
B
3
[Code] .......
I've tried to amend the formula's posted above but to no avail so am hoping someone can have a look at the attached.
I would like to be able to use Vlookup or similar to complete a table (starting in cell A19) based on the surname added in B16.
I love using vlookup, but what do I do if the value I'm looking up is listed more than once in my array? The default is that it will use the first value found.
I have a spreadsheet with 1000's of commodities and for each commodity the volume is broken out by month. For example, here's the sheet where I'm pulling the data from:
ITEMNOPERIODESTCSTREQQTYACTQTY
RC0202010010.109949140005867316
RC0202010020.109939312004668300
RC0202010030.109958968000
RC0202010040.109941769000
RC0202010050.109919656000
RC0202010060.109941769000
RC0202010070.109949140000
RC0202010080.109951597000
RC0202010090.109951597000
RC0202010100.109951597000
RC0202010110.109973710000
RC0202010120.109956511000
So my vlookup for the RC020 for the december Actual Quantity will default to the 5867316. For for January (period 2), if I do the vlookup it will still take the 5867316 again since its first in line.
So my question is, how do I alter my formula in January so it will vlookup the 2nd RC020 and give me the "4668300"?
I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.
A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15
I have another table (table2) in another sheet in the same workbook have a material and date.
A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008
My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.
I have tried using vlookup but it only works for the first match and doesn't check for other values
below is the function that i tried
=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")
I have a VLOOKUP that needs to bring back the sum of all entries that meet the criteria. At the moment it is only bringing back the first entry.
View 3 Replies View RelatedSo i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.
I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.
Writing this workbook in 2007, but it will be used in 2003. Have searched but can't find answer to this. I have a 5-column (A to E) table array and I'm using VLOOKUP in several other cells to return values in col2, 3, 4 & 5 with my lookup value being col 1. However, col 1 will contain numbers which occur more than once. How can I get VLOOKUP (or something else?) to pick the last occurence (lowest row) and use that one? Also, the array will be sorted - smallest to largest - as the rows go down - how will the SORT work on duplicate entries? I'd like, if possible, for the entry made most recently to be the lowest of the duplicate entries so that VLOOKUP (or whatever) will pick that one to use. Will any kind of SORT do that?
View 9 Replies View RelatedI have a dataset that looks like below -
Header1 Header2 Data
100 101 20.50
102 19
103 1003.2
101 100 20.50
200 300
102 100 20
200 104 30
Now, what i need to do is for each of the rows in my dataset, I need to find the reverse entries (consider first row where header 1 is 100 and header2 is 101, I need to search the entire dataset to find if I have some data for header1 is 101 and header2 is 100). In this case, I do have such data where header1 is 101 and header2 is 100.
Header1 Header2 Data Reverse Entry Difference
100 101 20.50 20.50 0
102 19 20 -1
103 1003.2 Not found Not found
101 100 20.50 20.50 0
200 300 30 270
102 100 20 19 1
200 101 30 300 -270
let me know if that's possible through excel formulas?
I need a column D with such data and column E for differences. So that, my final output should look like below -
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
BEFORE MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118
[Code] ........
AFTER MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911
[Code] ......
What the MACRO would look like? This is for Excel 2010.
I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows
Example (I have the below in a single Cell as column headers)
NCM Server Mgmt VLAN Site ID
Next Line down is the data (Each row in a single cell)
Enabled 10.10.10.0 50 TEST SITE 1
Enabled 10.10.20.0 50 TEST SITE 2
Disabled 10.10.30.0 50 TEST SITE 3
How I could achieve this as I have a number of projects where this would become useful
I know you can use delimiters but with spaces between the values I just can't fathom a way forward.
I have column A and it has 1000 rows, every row has a number in it, from 5000 to 5200, meaning that some numbers are presented multiple times in column A.
I need to lose repetitions, so every number is in the the table only one time and then I need to convert this one long column into, for example, 9 columns, so there's no wasting of space and have only one column in every page, if printed out.
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
I'm trying to identify duplicates with a return of "True" or "False". The attached workbook has column F for results (to read PI2 A if in column L, PI2 B if in column R or Both), but I want to query if everyone in column E is in column L or is in Column R and if so which one.
View 3 Replies View RelatedI have data like this
namefoodripeness
andyorangeC
andyyogurtD
andykiwiE
andystrawberyD
andyyogurtC
andybanannaA
andyorangeB
bobbanannaA
boborangeA
bobstrawberyA
bobkiwiB
bobyogurtC
bobpearC
stevekiwiC
stevebanannaC
steveorangeA
steveyogurtC
and i would like the data to look like this..............
orangeyogurtkiwistrawberyorangepear
andyCDEDC
bobACBC
steveACCA
not sure how to do it.
I would like to search for numbers and replace them with text (multiple entries).
I tried doing this with vlookup, but for some reasons i dont get the good values.
how i can set 'Data Validation' to stop me from entering duplicate values across multiple columns, i can set it for a single column i.e. A1 to A100 but i am after this but for something like A1 to H100.....?
I am currently using this formula in Data Validation:
=COUNTIF($A$1:$A$100,A1)=1
I need something to do this but for multiple columns.
I have a long table like this:
Car,date last washed
Civic, 11/1/2012
F150, 5/4/2013
Corvette, 7/3/2013
If I washed the Corvette, I'd add another row to it:
Corvette, 10/22/2013
And I would like to remove the "Corvette, 7/3/2013" entry. What's the easiest (read: least user intervention) way of doing this?
I'm trying to perform a search of over 15,000 entries using a wildcard. Sheet1 is a list of streets in the county where I live, and I would like to be able to type a part of the name that will return every instance of that name appearing.
I found a code which i have tried to adapt, but it is not working.
[Code] ....
The item being searched will be entered into TextBox1, and I'm guessing that the error I am receiving is in the .AutoFilter Field:=1, Criteria1:=TextBox1"*" line of code. This is the original sample I found onlne:
[Code] .....
where they were only looking for the letters "GB".
I have an excel spreadsheet that has about 40,000 rows....and about 30 columns across. The two columns I'm writing this about is column "PO#" and column titled "VALUE". The PO# column will have a number such as 4500234567...and the value column will have the cost of the PO#, for example $5,000.
Now within the the spreadsheet the PO# number and value will be listed multiple times....and there are hundreds of PO#'s listed, with its value.
I would like to perform some kind of filter on a seperate worksheet (? or whatever i needed) that would show the PO# number and value once.
I need to have an input box, although I need to have multiple entries within the box seperated by a comma, and then the filter displays only the entries entered in from the input box.
It lists single clients on single lines, with various column fields that have autofilters (such as where th client came from, when they arrived, who is dealing with them, are they complete and so forth).
The final entry I need to include is a list of th policies (it is a financial business) they need us to consider, split into three columns of life policies, pensions and investments.
My problem is that I am unable to put more than one policy in each of those columns which can then be sorted through the filters.
How am I going to achieve this, yet still keep one client per line?
I'm making a database for people to easily find their documents at work. In some cells there are multiple entries because the document could be within two catagories, eg. memo and report. Is it possible to somehow list these multiple entries within the cell so that the autofilter will recognise them as seperate entries and find that document whether you filter for memo or report? If not, can you get the filter to search more than one column for the same result and show all entries that are, say, a memo, even if it says so in different columns? I want it to be as simple as possible for the user so that they can select what they want from the drop-down menu and not
View 9 Replies View RelatedI am trying to count the number of indentical entries in my spreadsheet. I got the conditional formatting to color indentical entries that have more than 2 entries. I need to know how multiple entries are in my spreadsheet. I am using =COUNTIF($A$1:$A$10000,A1)>2.
View 9 Replies View RelatedI need to compile entries from several workbooks (one for each day of the month), from Column B every time, into a master workbook which will display all of the data from the columns B, laid out in individual columns in the master workbook. i.e. if Workbook1 contains the numbers 1, 2, 3 in cells B2, B3 and B4, and Workbook2 contains the numbers 4, 5, 6 in cells B2, B3 and B4, then I want the master workbook to run a macro which will put Workbook1's numbers into cells B2, B3, B4 and Workbook2's numbers into cells C2, C3, C4and so on for each day of the month. The idea is to be able to compile the month's data so as to plot charts per row.
Each month has it's own folder and each day has it's own workbook. File names are in the following format: YYYYMMDD.xls
I've tried several times, including the use of loop code found here: http://www.ozgrid.com/VBA/loop-through.htm but to no avail.
Is it possible to create a formula using a Data Validation List where it will add a new entry to the existing entries in a cell?
For example: I have a data validation list with the names of Pete, Chris, Bruce, Carly, Megan, and Becky. With the normal data validation set I can select one entry and it will be output into the designated cell. If I select Bruce then Bruce with be placed in the cell. Is it possible to set it up so that each time I click on data validation it will add an entry to the already existing entry? E.g. I have already selected Bruce then I decide to add Becky, I want the output cell to show both Bruce and Becky.
I know I can do this with a formula that will place all the results from different cells into a single cell, but I would then have to create multiple data validation entries of which I would rather avoid if possible. Not to mention I don't remember how to do this formula anyway......
My data set has a number of duplicate entries. But I would like to sort them out based on some conditions. Say for example my C2 code appears three time in the dataset. I would like to sort this multiple code using the time and i column.
Say for example, 871514 code appears three times in this dataset, and if this code appear within three years - time with a different (i column text e.g. public and private), I would like to exclude these three duplicate entries from my dataset. If this code appears within next three years from the date, but have same I column text (e.g.public versus public), I will keep them.
So each code will be considered based on three years of time and type of text in I column. If the code appears again later after three years, I will keep them.
So the codes need to be checked within three years time with i column text.
My dataset identifies the difference of dates between last entry and next entry.
I have a large database of service invoices. Since our invoice can carry multiple lines, the table in question might have multiple entries for the same invoice number. I'm trying to generate a performance metric dashboard for the service group by employee. Again, the employee can show up multiple times per invoice if he performs different work for each invoice.
I'd also like to condense the list down so there aren't any blank rows between the rows with data.
Ultimately, I need to find each individual invoice that each employee worked on and generate a list from which I can then generate an SPC chart. I'm willing to do this in a couple of stages if necessary. Primarily, I want to avoid using VB script if possible.
So, cherry pick service invoice numbers from a list when matched to an employee's name (VLOOKUP), consolidate multiple entries when that employee's name matches multiple entries of the same service invoice number, and condense the list (preferably as it's built) so there are no blank rows.
I'm trying to find an easy method of recording the available hours of operation for an airfield which can close a couple of times a day due to weather.
The attached sample explains it better.
I am doing a list which has the same products returning several times, but with different values. Want to filter/make a new list, with only one of each product and the summed amount of that specific product. Summing the specific amount is not that big of an issue, but the creation of the list is, least in a smart way i have tried this:
[Code]......
This being the last possible entry for the summed list.
My problem is that the formulas is getting too big for my computer :S, since this formula is copied more or less 10 times.
Here is an example of what i want: Product list.xlsx
I have a list of names that are mixed and in no order (and need to stay out of order, so not sorting allowed). I need to parse this list to give me the FIRST instance that each name shows up in the list. The one exception is that the first name, cell A1, will be auto-generated from a different workbook, and it is automatically named Name1.
So, in the example spreadsheet, Name1 (cell E2) must equal "Alice". The problem arises on Names2-4. Name2 (cell E3) should be David, Name3 (cell E3)=Jerry, and Name4 (cell E4)=Mark. I tried an array formula:
{=INDEX(A2:A13,MATCH(TRUE,A2:A13<>Name1),0)},
But it is yielding "Jerry" as Name2, when it needs to be Name3. And, of course, this doesn't work on Names3-4 at all. I don't think it matters, but just in case, A2:A13 will be data validation lists.