I have two lists. A1:A7000 has 7000 items and B1:B10000 has 10,000 items all in random order. List B includes all the items on list A, but with an additional 3000 items. How can I extract the 3000 items that are exclusively on list B? I would like to put these on a new list C1:C3000.
View 6 RepliesI work for a printing company that prints labels. We have 6 printing presses, lets call them A,B,C,D,E & F. Certain labels can only be printed on printing press F.
From our business system I can run a report in excel format giving details of all the labels that we have sold in the present month. I need split that report into 2 separate excel spreadsheets; 1 showing everything that was printed on presses A to E and the other showing everything printed on press F.
I also have another excel spreadsheet which gives a complete list of all the labels that have to be printed on press F.
Every label that we print has a part code and both spreadsheets show this part code so that would be the linking factor between the 2 sheets.
Looking to create a drop down list on the top of a column that will find all entries with that numerical value. For example, lets say I have four entries with the value 1234 spread out through the columns. When I click the drop down menu, it should only have 1234 once and when I click it, it shows me all the rows with that entry.
View 2 Replies View RelatedAn original text string entry appearing in an Excel cell would be:
"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"
I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.
Another example would be (very different from the first):
"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"
You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.
In a long string of characters and values, for example in B2:
Cos PGA 3.00 8.98 0.75 Cis TAK 1.00 2.99 0.25 Gsr PAM 14.00 41.92 0.82 Art GUN 3.00 8.98 0.18 Tlu NRG 9.00 26.95 0.33
I want to extract the second value after the specific string which is in in column headers C1, D1,E1...:
Art GUNCis TAKAxa MLPGsr PAMPen NMECos PGATlu NRGRot KGR
so for the example the result should be 8.98 in cell C2; 2.99 in cell D2; no value in E2; 41.92 in F2...
I attached a sheet with the table.
- In column A, I have list of items that take up 700 cells (i.e. the text is in cells A1, A2... A700).
- I have applied conditional formatting to these 700 cells, so some of them are now highlight YELLOW based on a criteria (i.e. cells A3, A14, A422, A654 are yellow)
- I want to create a list of these yellow cells in column B.
I've got a string of text (that can vary in length) and contains a date (could be any month)..
I'm trying to extract the date from the string using VBA.
For example, the string may be:
"The following price of the service is effective until July 1, 2012 and may change whenever"
I need to first determine if the string has a date in it and then extract the date....
I did a search on mrexcel.com and found the following formula that will tell me if the text has a date (returns true/false)...however, i'm not sure how to use this in VBA nor how to find location of date....
=SUMPRODUCT(COUNTIF(B1,"*"&N73:N84&"*"))>0
B1 contains the text, N1:N12 contain the name of the months....
I have in my cell a number, namely, 5260007005020024100055040300004110000000
What I would like to do is extract a set of digits from it,
Starting from the second 2, and shown here in the dots. I need all of the numbers in a separate cell, "52600070050200 .24100055.. 040300004110000000"
Hope this is clear. bearing in mind the number will remain as one so I would need to start at 14 then using LEN or something I'm not sure, extract the following 8 into another cell.
I have a list with rows containing NAME, CLUB and TIME (A5:C124).
I'd like to be able to create a new list which would contain the fastest 3 TEAMS along with the combined time (SUM) of the fastest 3 times for each CLUB. Not all CLUBS would have 3 entries and these would need to be excluded.
I have two worksheets. In the first one column holds the names of universities, and the next column holds employee names. For example:
London Metropolitan University | Chris Davis
London Metropolitan University | Sean Joesbury
London Metropolitan University | Stefano Carlino
London South Bank University | Simon Forsbrook
London South Bank University | Peter Lacko
University College London | Ben Burch
University College London | Oli Lan
In the second sheet, I'd like the universities to appear as headers to the columns, with all the employees of that uni to be listed underneath. I'd also like for these lists to be updated automatically as new rows are entered in the first sheet.
I have a column of DV lists. Is it possible for me to make excel, go through each DV list, and randomly select an item from each DV list?
View 1 Replies View RelatedI have two lists that I have compare to compare.
Expected result: Mark items matched in next to them, output unmatched items in column "J"
I started the code below. It properly marks the correct items, but I have trouble outputting them in Column "J"
Sub BNKREC()
Counter = 0
For i = 3 To 9
For J = 3 To 6
If Cells(i, "B").Value = Cells(J, "F").Value And Cells(i, "C").Value = Cells(J, "G").Value Then
[Code] .....
List 1:
Date Check# Amount
03/02/2013 100 $1,000.00
03/03/2013 101 $1,045.25
03/05/2013 102 $280.56
03/06/2013 103 $456.31
03/08/2013 104 $250.55
03/15/2013 105 $2,456.12
03/25/2013 106 $844.76
List 2:
Date Check# Amount
03/02/2013 100 $1,000.00
03/05/2013 102 $280.56
03/08/2013 104 $250.55
03/25/2013 106 $844.76
Expected result:
Un-Matched List:
03/03/2013 101 $1,045.25
03/06/2013 103 $456.31
03/15/2013 105 $2,456.12
I am trying to create code for a Macro that will compare items in two seperate lists in a spreadsheet, and eliminate all entries that do not appear in both lists.
I've got this table that I want to sort. I've got it formatted as a table in Excel 07.
ABDescription
11Sunbake
24Make Sandcastle
35Apply Sunscreen
46Sip Coconut Mocktail
52Play with kids
66Seashells
72Starfish
83Swim
91Snorkel
2Surf
3Boogieboarding
4Eat Icecream
1Play beach volleyball
I'd like to sort the table where all the 1's are together and in line with column A ie......
i am looking for formula in my macbook pro excel where i have purchase apple 50kg for $50 and grapes 20 kg for $30 and orange 10kg for $15 and am doing this daily and in end of month i want to see separate kg's and amount for whole month and later year of apple, grapes and orange without sorting it
View 1 Replies View RelatedI 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.
I 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 a spreadsheet (rows 1-15) where column A is a list of items in ascending order, and column B shows the catalog# that the item appears in. An items can appears in multiple catalogs.
I would like to develop a spreadsheet(rows 18-21) that shows the items that appears in each catalog.
The items should still be in ascending order.
The order of the catalog# row18 does not matter.
I have several hundred items and 30 catalogs....
I'm trying to create a module that will take selections from an activeX Listbox on a Sheet called Setup and transfer them to another sheet called Lists. On the Lists sheet, I want to store the selected items in cell L2 and down. So for example, if there were 4 items selected from the Listbox, cells L2:L5 would be populated with those values.
View 1 Replies View RelatedI have a userform and on this userform i populate a listbox with values.
What i want to know is how do i extract ALL the items in the listbox.......not just the ones that are "selected".
What criteria can I use to filter a list on and item and the latest date?
I need to extract a list of all items but only with the latest date of each ...
I am working on putting together a very large spreadsheet covering multiple data sets over multiple states/years. I am trying to convert the data that I have in one spreadsheet (that is arranged like the example below) and make it so that I can paste the data into another spreadsheet as one single row: i.e, 1,651 would follow in the column to the right in the same row as 6.4 and so on. Right now I am having to copy and paste row by row and it is going to take me years.
3,484
2,179
62.5
2,053
58.9
126
5.8
5.2
6.4
[Code] .......
I've been given a long list of mailing addresses in Excel in formatted as a single column, each address using three rows with no blank rows. Example:
John Doe
123 Maple St. #2
Cambridge, MA 02139
Jane Smith
321 Elm St. Apt 24
Austin, TX 34557
etc.
etc.
I need to get this data into the form ...
John Doe 123 Maple St. #2 Cambridge MA 02139
Jane Smith 321 Elem St. Apt. 24 Austin TX 34557
I would think this has been done, but I've searched the web and this forum without success.
I need to extract numbers from excel cells. For eg-
BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694
^^^ This is one of the cells. I need to pull out 04.00, 2.25, 26.50, 1340 into different cells from one given cell.
I have tried using =MID() function, but I need to make changes into that formula everytime the position of first numeric letter changes while I go along a column. And I need to make changes into it as I go along the row.
It is taking me a lot of time in extracting numbers from one cell into different cells and I am afraid that I will spend the rest of my life getting this stuff done.
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.
I find writing out what I want helps some times so let me try it here.
So my variables are:
Product (there are 22 products)
Supply Less than (inset number)
These are the two criteria I want to use to produce a result.
The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.
What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.
The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
I want to be able to use list as filters that will show me results for prices I need. I have no idea how to figure this out. I attached a sample file so you can see what I'm trying to do.
View 4 Replies View RelatedSuppose I'm a grocer and I use a spreadsheet to track my inventory. I sell fruits, vegetables, and dried spices. On one worksheet in my Excel file, I create the following lists:
FRUITS
VEGETABLES
Apple
Potato
Orange
Celery
[Code]...
Then on a second worksheet, I have my actual inventory, which is this:
ITEM
QNTY
PRICE
CLASSIFICATION
[Code]...
I need a formula for the "Classification" cells of the Inventory. For each "Classification" cell, I want the formula to do the following: Search for the corresponding "Item" in the "Fruits" list; if found, then put "FRUIT" in the Classification field.If not found, search for the corresponding "Item" in the "Vegetables" list; if found, then put "VEGETABLE" in the Classification field.If still not found, put "SPICES" in the Classification field.
So using my toy example:
The formula for the first row would search for "Banana" in the "Fruits" list. Because the desired text sting is found, the formula enters "FRUIT" into the Classification field.The formula for the next row would search for "Cucumber" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is found there, the formula enters "VEGETABLE" into the Classification field.The formula for the next row would search for "Dried Oregano" in the "Fruits" list. It would fail to find it, so then it would next search the "Vegetables" list. Because the desired text sting is not found there, the formula enters "SPICES" into the Classification field.
If Excel can handle this level of automation, but I suspect there must be a way to do this.
On about 30 worksheets I have lists of names. These names are changed and added to now and then. I would like them to automatically populate a worksheet called "list". Preferably the names of every sheet with a blank cell at the bottom.
The whole work book is about 60 worksheets.
I have a workbook with two sheets
sheet1(Invoices) is the master and sheet2 (GR) contains information that i need to put onto sheet1.
I need a formula that will allow me to lookup sheet2 based on the criteria from 3 columns on sheet1... (PO no, Part No, Qty)
At the moment I am using 3 different vlookups to achieve what I need
If there are records on sheet2 which matches sheet1 then I would like to have the word MATCH if there is a match and CHECK if there is not?
We currently have a few hundred vendors we order from for a retail business. We receive price lists in excel format, but they all differ in how they are formatted, i.e. one may have a column header of "UPC" and the next may say "UPC ID" and they may be in different columns. We currently format a handfull of these price lists so they have the same headers so we can easily compare costs, margins, etc, but we don't have the resources to do many more than this.
View 6 Replies View Related