Extracting Values From A List?
Jun 15, 2009
Sample Data:
Col A: Col B:
CV01 01
CV01 01B
CV01 02
CV01 08
CV01 03
CV01 10
CV02 02
CV02 02B
CV02 03
CV02 10
CV02 01
CV02 09
CV03 06
CV03 06B
CV03 04B
CV03 05B
CV03 08
CV03 09
I need to generate/extract a "list" of all the values in Col B. that "belong" to each value in Col A. So, if my criteria is "CV02," I need the list to be 02,02B,03,10,01,09 (with each value in it's own cell)
I've tried the "VLOOKUP" function, but it only returns the last value for each criteria (example =VLOOKUP(CV01,A1:B18,2) returns "10")
I've searched these forums, but I'm not really sure what search criteria to use...
View 7 Replies
ADVERTISEMENT
May 23, 2013
I have a name in Column A, and a number that goes with it in Column B. Some of the names on Column A are duplicates. I want to extract a new list that will add the values in Column B of all matching names on Column A, so that my list has only unique names and a total of the numbers in Column B.
A:A B:B
SPP-001 2
SPP-002 3
SPP-006 4
SPP-001 5
SPP-008 9
SPP-006 3
Result that I'm looking for"
C:C D:D
SPP-001 6
SPP-002 3
SPP-006 7
SPP-008 9
View 6 Replies
View Related
Apr 11, 2014
It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.
What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.
I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.
I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.
View 8 Replies
View Related
Dec 21, 2012
I have a list of tweets, and I would like to extract just the username found within the tweet. I'm not sure if a formula is the best way to do this, or possibly a macro.
The example I've attached is only of 18 tweets, but the spreadsheet I'm looking to use this on is 100,000+ tweets, so I need something easily scalable. One thing to note is that a tweet can have more than oneuser mentioned in it, such as the first tweet in the example.
Tweet Example.xlsx
View 13 Replies
View Related
Jun 27, 2012
I have a table such that column A has a list of names, column B shows either "Yes" or "No" depending on if that person's info needs updating. I am trying to find a way to a populate a list (prefereably in column C) that has only the names of people from column a that show a yes in column b.
I know that i could just do =if(B2 = "Yes", A2, "") and then drag the formula down, but that leaves blanks in between the cells. If column A has 10 names and only 3 are Yes then I only want the first 3 cells in column C to be populated with the corresponding names.
I think I need to use a combination of INDEX and MATCH functions but i dont know how to do that with a criteria that falls in another column.
View 14 Replies
View Related
May 3, 2013
I have attached the worksheet i am currently working on -snapshot of a 2.9 mb file :-).
I have a table i need the information compressed based on the values being greater than '0'. The original table Column A-E runs down 15000 rows but i am only interested in the values that are greater than '0'. I would like to see G3 to contain the first cell from A to contain a value greater than 0, G4 to contain the next sell with a value greater than 0 -- so on and so on.
This method will apply to columns G-K, their may be another formula that i am unfamiliar with that will get this to work.
I have already tried to play around with INDEX and MATCH, i think i am on the correct path but i am not looking at something the right way.
View 5 Replies
View Related
Nov 15, 2013
I want to extract text from column A into column D,E,F based on condition on column B. I am able to do this by IF statement, but the data is not continuous. I want to extract continuous data into D,E,F columns without any gaps.
Condition for Column D: >1
Condition for Column E: >0.5<1
Condition for Column F: <0.5
I am attaching the sample list for reference.
View 7 Replies
View Related
Apr 20, 2009
Suppose I have a list of data in which I want to retrieve or extract specific row from the list as per operator name & relative comments (Done or Not Done) from the given list as per the operator name, now what I want that my VBA code should be able to get the operator name & the relative comment data from the list according to the operator name in a new sheet. My data keeps changing so I need a code which can work on any size of the data. I need to retrieve only operator name & Comment column in a new sheet to run my Pivot table.
My Excel data is something like in the attachment & the result I want through VBA Code.
View 15 Replies
View Related
Jan 3, 2009
Is there a way I can get a continuous list, ie no missing rows, from a much larger list. (New list in separate columns to the R of the list.) I want to extract data from column A, only if the data in col C matches my specified criteria - I want this info to appear as a list with no gaps, ie I can do this easily using "If" but there will be lots of blank rows doing it this way.
Furthermore, as I need two different lists from the same data block, I need to specify whether number (extract these to col N) or text (extract to col O).
View 3 Replies
View Related
Apr 4, 2008
I need a formula (I'm not sure if its possible without using VB which I have found on another thread) that I can 'drag' down to the cell Bx (where x is variable) to extract unique entries in a list of values and then display them in a shorter, more concise list - like the 'Pick From List' function does but actually display these values in seperately in a column of cells.
Hope this is making sense.
So (hopefully) to elaborate:
A1:Ax = The list of values containing duplicates
B1:Bx = The list of only unique values from column A
View 9 Replies
View Related
Sep 23, 2013
I know that there are programs out there that will convert a PDF into excel, but what I need to know is, is there a way for excel to access values (numbers) in a PDF and then use those values in a worksheet?
So, if I have a table in a PDF file - containing part numbers, descriptions and quantities ordered, I want to be able to extract the part numbers and QTY, and have them populate an excel table?
View 11 Replies
View Related
Nov 15, 2013
I am using Excel 2010 on Windows 7. Here is a link to the worksheet I have a question about : Example.xlsx
The only two relevant sheets here are "TestScores" and "Area 1." What I am trying to do is copy names over from the TestScores sheet to the Area 1 sheet based their test scores. It is important to know that a passing score is anything 85 or above. If you don't pass Phase 1, you move onto Phase 2, and if you don't pass that you move onto Phase 3.
Let me first explain how the TestScores sheet works. Here is a screenshot of the sheet:
I copied in column C, the names, from a roster sheet. Columns A, B, and D all VLOOKUP information from the roster as well. You can ignore columns E & F. Column G is just a test name that is manually inserted. They are all in the same order since this is an example worksheet I am doing the program on before I input the real data. Column H is also manually inserted, with data validation to only allow values 0-100 and a few different relevant terms (MS, EXT ABS, N/E). These next two rows are the formulas in columns I and J, starting in row 3.
I=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(H3="","",IF(H3>=85,"N/A (PASS)","Need")))))
J=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(I3="","",IF(I3="Need","",IF(I3>=85,"N/A (PASS)","Need"))))))
What these formulas do is the following: If N/E, MS, or EXT ABS is in column H, it copies those over to the next two columns. If someone scores 84 or below, it says "Need" in the next column. If they score 85 or above, it says "N/A (PASS) in the next column(s). The user is meant to write over these to insert the phase 2 and 3 scores.
Here is the "Area 1" spreadsheet I am working on that has the problem.
First off, I created these formulas by comparing them to another post and replacing my ranges and criteria. In column A, it returns all the names of Team Members who passed in Phase 1 (have a score 85+ in "TestScores" column H). The array formula is below and it works great.
{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$H$3:$H$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(A$6:A6))),"")}
In column E I have a similar formula, except it returns the names of those Team Members who have "Need" in column I of "TestScores." This formula also works great. The formula in column D is just a VLOOKUP based on column E and works well, too.
{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500="Need",ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(E$6:E6))),"")}
Here is where my problem is. In column F, I want to return all the names of people who passed Phase 2, which means they have a score of 85 or above in column I of the "TestScores" sheet. However, not only is it not returning the right names, I can't even figure out what criteria the names is returns has. The current list, in (mostly, oops) red, is incorrect. The formula, below, is almost the same as the formulas above, with only 1 difference in each case.
=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(F$6:F7))),"")
It refers to column I, instead of H, compared to the formula in column A. Its criteria is >=85 instead of ="Need", in comparison to the formula in column E. Nonetheless, it still isn't returning the right names! The other weird thing, is if I replace the ">=85" with "=90" it will return the names of team members who scored 90.
View 3 Replies
View Related
Oct 4, 2006
I'm trying to do is build a form that will allow me to select from a list of options, that links back to a catologue of data so that when i click on the generate button it will pull the data associated to the item selected from the list into a text box in excel. I have attached the form that I have created.
View 4 Replies
View Related
Oct 20, 2007
I have certain values relating to a single person spread out in various worksheets and I need to get that in one sheet.
eg: For "NAME xyz" I need the values corresponding to "apples" in as many 10 sheets. Similarly for about 20 different things for about 300 names.
View 14 Replies
View Related
Jan 9, 2014
This is my qns
cell A1 having <txt>NEWM</txt>
cell A2 having <txt>RSQ</txt>
I need to display NEWM in B1 and RSQ in B2....
View 2 Replies
View Related
Sep 3, 2008
Does anyone know of a formula that will take an expression such as
"C1-5" in a cell, and produce five separate values in 5 cells:
c1
c2
c3
c4
c5
Even a starter would help, like how to extract values from "1-5"
Ultimately I'd like to use this for values that can have up to 3 digits following the letter (ie, C99-112.)
View 9 Replies
View Related
Oct 1, 2008
extract the highest top 5 values from one column in a filtered table with hidden cells. How can I do this without using advance filter or a pivot table in excel 2003 that will show only the visible cell values and not the hidden cell values
View 9 Replies
View Related
Mar 1, 2009
I have fixed amount of columns (6 in example: can be seen at sheet called "llist1", more in real example:"real example of list1") and rows (8 in example, 62 in “real example").
These 6 "analyzed" columns consist 3 type of info, basically there were 3 columns, which were "cut" to 6. In my example those types of info are called "name1"(columns A and F), "name2"(B and G) and "a or b"(C and H). Also there are rows, which should not be “analyzed”.
First, according to example sheet called "list1", i need to extract unique values from some "fixed" rows of columns A and F, more detailed: a2:a4 a6:a8 f2:f4 f6:f8. Probably, a named list should be created. Also it would be great, if these names will be extracted in alphabetical order. This is the first problem.
Then there goes second, the big one, problem: I need to extract unique values from !"rows of fixed rows"! of columns B and G "for" extracted unique values from columns A and F.
It is really easier to understand if you look at the analyzed information structure at the example sheet and what I want have in the end at "ideal" or "wanted" sheets.
By saying "rows of rows" I mean something like this: cell B2 contains this:
1st "row": abc-301
2nd "row": abc-302
3rd "row: abc-303
All this rows, thanks god, always contain 7 signs. I suppose a second named list is needed here, i e: b2:b4 b6:b8 g2:g4 g6:g8
After that I need to count A's or B's, according to rows, already done that myself using asterisks.
how can i attach an excel file? checkick the faq info, the spreadsheet will appear in a couple of minutes...
View 9 Replies
View Related
Jun 15, 2009
I sent this before but the formula I was given wasn't correct (as you will see from the file). In Column B of the second sheet (Data) of the attached file, I want to bring back the relevant name form the first sheet (List), which involves searching through the long descriptions from column A on the Data sheet for the name on the List sheet.
View 2 Replies
View Related
Jan 5, 2005
My colleague has a problem where he needs to Extract numbers from alphanumberic strings. Here is the formula he is using:
=MID(B7,MATCH(TRUE,ISNUMBER(1*MID(B7,ROW($1:$297),1)),0),COUNT(1*MID(B7,ROW($1:$297),1)))
It works if alphabetic and numbers are clustered together such as scs987
It works if alphabetic and numbers are not clustered together such as scs987dtg
Problem: It does not work when numbers are not clustered together such as scs987dtg1234
View 8 Replies
View Related
Aug 18, 2009
This how it looks like, i used this code (see below) to get the amount if there is a word "ins" (martin's code)
IF(AND(ISNUMBER(SEARCH("ins",B47)),NOT(ISNUMBER(SEARCH("insp",B47)))),LOOKUP(99^99,--("0"&MID(B47,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B47&"0123456789")),ROW($1:$10010))))+0,""))
it works pretty great, but when things getting better, i've encounter this data: 12,300 ins 09-10 flood
so what i did, is to put another if function, like this: ..
View 8 Replies
View Related
Feb 21, 2008
I have a spread sheet with following:
Name|Address|Home Phone| Cell Phone| Status|Project Name|
I want to select name and extract only home phone and cell phone nos.
Please help me with a code. I have about 2500 entries.
View 14 Replies
View Related
Oct 14, 2008
I was given a worksheet that contains a column (A) containing about 5,000 URLs (A1 to ~A5000). Each URL string includes three parameters that I want to capture the values.
For example =
[url]
The prameters are known but the values are random up to 256 characters.
I am trying to pull each of the values into a column for sortability and have been mildly successful using MID and FIND but no joy.
View 9 Replies
View Related
Feb 28, 2014
I have an excel sheet with data results of samples analysis from ICP-MS the sheet includes the name of the sample and its results, I want to extract for each sample only the ( average value "x" , standard deviation "S" and " %RSD) and sort these values in columns , see the photo and the excel sheet attached ..
View 13 Replies
View Related
Jul 11, 2014
I currently have a macro that looks at one value and copies the data to another worksheet.
I'd like to modify it to look at two different values and then copy the data. I've tried modifying the VBA code, but can't seem to get it to work. In the "ETF Report (1)" tab, i want it to look at the "ETF Report" and "PO No" values.
The Final Version tab is what i'd like the results to look like.
View 4 Replies
View Related
Aug 3, 2012
Any code that can extract the alpha numeric values from sample spreadsheet below?
Where in col A "SCn" is extracted (or copied) and then pasted in col H?
n = 1 to 99
That is - from this:
A
B
C
D
E
F
G
H
I
J
K
L
[Code] .........
To this:
A
B
C
D
E
F
G
H
I
J
K
L
[Code] ......
There are thousands of rows to extract the alpha numeric values from.
View 6 Replies
View Related
Jul 8, 2014
I am trying to transfer data from some appointment based software into a spreadsheet .
the data comes out as one row For example ,the following is the contents of cell A1: 06/06/2014 09:00 AM - 09:30 AM Patient: John Smith
What I would like to do is extract one column with the date ,one column with the patient forename and one with the patient surname.
I have tried various combinations of =RIGHT(A1,LEN(A1)-FIND(".",A1)) etc etc but cannot extract the data I need
View 3 Replies
View Related
Feb 6, 2013
I have a workbook with two tabs, Summary & January. The january tab has total expenses for the month of january that are subtotaled by last name. i.e. (baker total, decker total..). I would like to be able to use a formula on the summary tab to pull the values of each of the totals for each person from the january tab to the summary tab.
I tried on the summary sheet doing =jan and selecting the first persons name and then going back to the summary tab and dragging that formula down but it pulls all of the other data within the subtotal on the jan tab and I just want each persons total.
View 5 Replies
View Related
Jun 2, 2013
I am trying to extract values from a text string and add them up in Excel 2007. So far i have been successful in extracting the value out of the text string like this - =MID(I6,AD6,3) where AD6 holds the position number in the text string to start from. So it's working OK for one row but i need to do the same thing on multiple rows where the text string can be in different columns and I'd like it to automatically pick up the non-blank cell.
each row only has one column with text in it and the value i need to extract is always after "$". this is a working spreadsheet so the text string could move from column to column over time and I'd like my formula to be able to detect which column to read from. I then need to add up all the values from each row.
View 9 Replies
View Related
Mar 20, 2014
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
View 4 Replies
View Related
