Filtering Data In 2 Seperate Columns
Feb 25, 2007
I'm trying to put together a sheet to track football results. For simplicity sake, the sheet looks like this...
Home ; Away ; For ; Against ;
a ; b ; 2 ; 0
c ; d ; 1 ; 1
b ; c ; 1 ; 3
d ; a ; 2 ; 4
a ; c ; 1 ; 2
By using Autofilter on the home or away columns, i can obviously filter it in order that I can see all A's home results or all of A's away results, but by doing this you can't just filter the sheet in order to see the results of every game A plays in. I have a feeling that I need to do something with Advanced filter but have been playing around with this and not getting any joy. Can anyone advise on what I need to do?
Ultimately, my end goal would be to be able to create a drop down list where i would select the team from a list of all teams and this would automatically filter the sheet to their results only (and then I could use an autofilter to look at just homes or aways).
View 9 Replies
ADVERTISEMENT
Jul 23, 2009
Attached file where i m not able to split data in seperate seperate coloumn
View 3 Replies
View Related
Nov 30, 2008
See attached spreadsheet. I need to have all the data which is held in 1 row in column A seperated into its own column automatically. The order i need the data to be in is as follows
Date(pink), Time(red), Racecourse(green), Race Type(black), Class Of Race(purple), Going(blue), Prize Money(yellow), Distance(grey), Number Of Runners(brown). the text in bracket is a key for the info for your ease of use.
View 3 Replies
View Related
Mar 28, 2014
how to filtering data from the columns please (not the rows)..?
Usually we could filtering the rows with auto filter, right..?
Now, is it possible to do that with columns..?
View 8 Replies
View Related
Dec 10, 2009
I have text in column A and text in column B. I would like to create column C and place the number 1 in each row whenever columns A and B contain the text I am looking for. I could then use C to filter all the 1's with ease (by way of macro or the autofilter). In my problem, column A contains the following text per cell:
B
B
B-A
B-A
B-V
B-V
Column B contains:
THR
THR-MATT
HF-MATT
HS-HS-THR
HS-MATT
I need to filter based on column A having the letter "A", or column B having the word "MATT". Columns A and B do not have to contain both "A" and "MATT", respectively, in order for column C to have a 1.
View 4 Replies
View Related
Sep 11, 2006
Now i have one excel sheet with two separet sheets in it ( Sheet 1 ) and ( Sheet 2 ) . I have in sheet 1 a column A with material codes and ColB is discreption and Col C IS Prices . But in Sheet 2 Col D is materila Code and Col I is a price .
the recordes in sheet 1 are around 11000 but in sheet 2 are around 2200 where the sheet 2 has a specific materials from sheet 1 .
Now i want to update the prices in sheet 2 from sheet 1 for each item after confirm that the materila code in sheet 2 equal in sheet 1 so copy the price from sheet 1 to sheet 2
Sheet 2 is old prices and sheet 1 has new prices so i need update the new prices in sheet 2
View 9 Replies
View Related
Sep 16, 2008
This should be an easy one, it has slipped my mind.
I have columns A and B, I want to find any names that appear in both column A and column B. I want them to show in a certain color.
View 11 Replies
View Related
Jul 20, 2009
I have a lot of carton dimensions that are always presented in the same manner and would like to be able to split the individual dimensions into seperate columns.
The format is always: Length Width Height. Presentation of data is always ???x???x??? or ??x??x??? (ie two or three chrs seperated by the letter X). If the full string is stored in column A I would like B to display Length, C the Width and D the Height. I find it easy to use LEFT for Length but struggling with Width and Height which I'm sure are easy, just can't get my head around it.
View 4 Replies
View Related
Aug 16, 2008
I'm glad founding this great forum. I have learned a lot in the last few months.
Now I need to do a task and can't find online a tutorial for this. Here is what I need:
File A contains 23 columns with info for 300 products (rows). File B contains same columns for 280 out of the 300 products from file A, plus 40 new products.
I need to flag in file A the 20 products that are not in the file B, plus flag in file B the 40 products that are not in the file A.
The structure, columns, etc are the same for both files.
View 6 Replies
View Related
Apr 15, 2013
I import a text file that displays outstanding parts orders. The order Number column only displays one number even if there are multiple parts ordered I.E. so if three parts are on order then the order number is displayed once with the other two underneath blank. I would like to filter the column that displays the amount of stock that is available so that even if the row has no stock but the other two have all three rows are displayed that reference the order number.
Example Screen
Example.jpg
View 5 Replies
View Related
May 13, 2009
I have an excel with the following information
A1 - Is a dropdown which will have names of the systems (EAI,Mediation,CDF
..,All)
Row2 will be header which will have following data
B2 - Project Id
C2 - System ID
D2 - Project name
E2 - Project Phase
F2 - Status
from g2 to GN2 there are about 60 resources names entered
From C3 to F3 the data wil be entered and appropriately a chk mark will be
made to the corresponding resource wroking on that project
Say i have about 10 systems provided as dropdown for entering data in A1
(Ex: EAI, MEdiation, .....)
There are resources working for each system
when i apply filtering on a system name i shoud be able
to show only those resources working for that system and all others should be
hidden
Also under the "system" dropdown i will have an option "All", when "All"
option is selected i should be able to see all the data for all the systems.
EX: if i take system "EAI" and if the resources working for eai are there in
AO2 to AX2 then i want the resources from AX2 onwards and before AO2
not to be shown.
If i take a system "Mediation" and if its resources are from Z2 to AI2
then i want resources before Z2 and after AI2 notto be shown.
If "All" option is chosen then i should be able to see the whole sheet
with all the data for all the systems and all resources.
View 12 Replies
View Related
Jan 28, 2008
I am trying to filter three columns of data to a given range without having to using auto filter on each column. I would like to have a Macro or Control Button or something easier than picking each set of data each time I want to filter.
Attached is a sample file.
I am not well versed in Excel and this is my first post so please bear with me in this process.
View 14 Replies
View Related
Jul 28, 2008
a formula in excel. here's what i need to do:
column A has the names of deffirent drivers (ex; john, peter, james)
column B has start time (9:00, 10:04, 11:08)
column C end time (9:14, 10:16, 11:23)
i need a formula that will take all the "john" from column A, do column c - column b for the time it took and then do an average of all of john's attemps.
View 10 Replies
View Related
Mar 4, 2009
I have requirement wherein, I have to put filter conditions on two separate columns in the same sheet & putting hard coded value eg. "XYZ" in third column for this selected range. I am using macro autofilter but its giving me errors for multiple filters.
View 9 Replies
View Related
Dec 1, 2009
I have filtered a worksheet and want to only total the data in the columns that are visable, is this possible?
View 4 Replies
View Related
Jul 18, 2012
I have a worksheet with
Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)
There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.
I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.
For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.
I am using Excel 2010.
Name
Primary
Secondary
Tertiary
Person 1
x
[Code] .........
View 3 Replies
View Related
Jan 22, 2013
I have a spreadsheet where I need to lock certain cells, only problem is this then takes away the ability to filter the columns which I also still need in that spreadsheet.
Is there a way to lock cells so that they can not be edited but also keep the filtering function?
View 2 Replies
View Related
Apr 18, 2014
I want to filter data code which contains public IP Address and copy the filtered data to other sheet. Column contains both string and number. Please see example:-
ABC
http://192.168.44.29:2967
DENIED
BCD
https://d31qbv1cthcecs. atrk.js
DENIED
[Code] ........
In above table (second column) the URL address after http:// can start with string or can start with number. If it is starting with numbers the I need to check whether the address is public is Private or public. In second column http://192.168.44.29, http://172.25.5.107/is Private IP so I need to ignore it but http://95.138.166.181 is public IP and we need to copy to other sheet.
View 5 Replies
View Related
Feb 13, 2008
I have two seperate worksheets and I want to merge the data into one new worksheet.
example:
Book1 contains;
stock code
description
price
Book2 contains;
stock code
manufacturer
item name
I need to make one new worksheet which contains;
stock code
manufacturer
item name
description
price
I can not copy and paste because they are not in the same order and I can not sort to put them in the same order.
So I need to merge data using the stock codes into one new worksheet.
View 9 Replies
View Related
Jan 11, 2010
I have three columns (9, 10, & 11); text, currency, and text and I am trying to move them from seperate columns on Sheet2 to one column with forward slashes "/" between them on Sheet3. The attached code does this, however I lose the dollar sign and commas.
View 4 Replies
View Related
Jun 23, 2013
I am somewhat new to excel and have a task I have a catalogue that I need to separate multiple printer models that all have the same part # into individual line items. This will save me weeks of work.
I have this:
Model
part
DCP-7020/HL-2030,2040,2070N/MFC-7220,7420,7820N
TN350
And want to end up with this:
Model
Part
DCP-7020
TN350
HL-2030
TN350
HL-2040
TN350
[Code]...
Or even this:
Series
Model
Part
DCP
7020
TN350
HL
2030
TN350
[Code]...
View 5 Replies
View Related
Feb 9, 2009
I've got several sets of data all which link to seperate charts, the problem i'm having is that i dont want the chart to display the fields with zero values and with the amount of charts and data it is taking ages to re-source the data is there a way of getting the chart to ignore zero fields or of somehow sorting the data in a way only fields with values are shown
View 9 Replies
View Related
Oct 10, 2007
I have a data sheet of 190,000 lines. It is a telecommunications bill with has approx 15 columns.
I need to separate the spreed sheet into the respective "call types" and put this data onto separate tabs, at the moment I do it all manually with Excel 2007.
Is there a way of automating this separation of data
View 9 Replies
View Related
Mar 3, 2008
I have built a simple little logger forlogging insurance policies set up without permission, at the moment it logs all data in sheet2 of the active workbook. What I want it to do is when submit is clicked is to export and save the data to a seperate (central) workbook to collate all entries and not to save it in the active workbook. I've inlcluded the file and the code is as follows;
Private Sub CommandButton1_Click()
Dim ws As Worksheet, lRow As Long, Str As String
Set ws = Sheets("Sheet2")
lRow = ws. Range("A" & Rows.Count).End(xlUp).Offset(1).Row
'____error handling______
If TextBox1.Value = "" Or NullString Then
MsgBox "Ooops, it seems you haven't entered a policy number!", vbCritical, "Error..."....................
View 2 Replies
View Related
Mar 5, 2009
I have a list of names that I'm looking up, and I want to list the data relevant to each name in a seperate place using a VLOOKUP ,(maybe theres a better way to do it) but some of the names appear more than once, resulting in an error.
I have on the first sheet, Name, Customer, Amount and Variance.
On the seperate sheet (in the same workbook) I have then name with 4 VLOOKUPS underneath, trying to extract the data Customer, Amount and Variance. But, I get errors in all of them.
Is there anyway I can List all the data relevant to each name using a Lookup function or is there another formula I should be using?
View 4 Replies
View Related
Nov 29, 2006
How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.
View 4 Replies
View Related
May 12, 2008
I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.
View 9 Replies
View Related
Aug 14, 2014
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
View 14 Replies
View Related
Aug 31, 2013
I have a two sheet, in Sheet1 i have a data, and in sheet 2 i have a count of location wise data in it, which is from sheet1.. And beside it u will see a button "View Detail". When you click on that button it will pop up a userform which have listbox and 2 button.. Now in listbox i get a filtered value of column C of sheet1 (For eg. Mumbai, Pune, Chennai).. When i click on the value (Mumbai) in Listbox and then click on View Location Details. It should give me a details of mumbai based candidate, like name, pod no, location in the range of (H5:J100) of sheet2..
I wrote the code for it but its not giving me the correct data..
View 1 Replies
View Related
Aug 19, 2012
is there any way to NOT Displaying Items with No Data when filtering data in my pivot table?
For instance, I have 2 report filters: Category and Subcategory, when I select a category in the first filter I want to see only the options of subcategories with data in the second filter, I mean display only the subcategories of the Category previously filter.
Same scenario I have with a report with Directors and Organizations, when I filter one Director it would be nice to see only the organizations of this director and not all options on the data.
View 1 Replies
View Related
