Filtering Data Using Two Separate Columns

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:


Column B contains:

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


Filtering Data On Columns?

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

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 View Related

Subtracting 2 Columns Of Data From Each Other When Labels In Separate Columns Match

Feb 20, 2009

I have four columns of data, as follows:

label 1, value 1, label 2, value 2

I need to create a formula in the fith column that for each line will tell excel to:

look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.

I have tried doing this with SUMIF but am getting nowhere fast....

View 7 Replies View Related

Filtering To A Separate Worksheet

Jul 25, 2006

I have a spreadsheet which is a list of dates and amounts.

I want to have a filter on a second worksheet wherein I can type the date and it will filter all amounts for that date.

I've tried the Advanced Filter but I can't get it to auto filter when I change the date. Also it needs setting up every time I do an advanced filter.

View 9 Replies View Related

How To Separate CSV Data Into Different Columns

May 22, 2013

how to separate .csv data into different columns in excel?

e.g this .csv data



View 11 Replies View Related

Separate Data Into Columns?

Jul 22, 2014

I have some data which i want to split, i have tried " Text to columns "on excel but couldnt find a way it works.In one cell my data is exactly like this:

Name of product
Code of product

Loc.: here is the location
expire date:

I would like each one of these itens on one column, like

A b c d
Name of product code of product Loc.:here is the location expire date:

View 11 Replies View Related

How To Separate Data To Different Columns

Jan 28, 2014

I have a huge data and want to know the best VBA or formulas to separate to consecutive columns.

Example :


[Code] ..........

View 3 Replies View Related

Lookup Data From 2 Separate Columns

Mar 28, 2014

I am working on a workbook, it's not my workbook so I can't change how it's laid out.

I am trying to figure out a way that excel will check 2 different columns for a code that is manually entered, then enter the codes description in another cell.

It's basically a deposit and expense ledger.

One column has expense codes and names, the other deposit codes and names.

I want to be able to enter the code letter into a cell in the ledger and excel will enter the code description into another cell.

View 11 Replies View Related

Separate Data From One Column To Several Columns

Jan 3, 2014

I'd like to copy information from a PDF into a worksheet. The information looks like this:

1 PAOZZ 5310-00-918-0482 80205 NAS1291-7 NUT.............................................................................. 1
2 PAOZZ 5305-00-995-2125 80205 NAS603-7P SCREW.......................................................................... 2
3 PAOZZ 5305-00-866-0937 80205 NAS603-8P SCREW.......................................................................... 14
4 PAOZZ 5306-01-106-8238 80205 NAS6603-3 BOLT ............................................................................ 8

When I paste it into excel it all gets entered into one column, I'd like to spread it out over 7 columns. Is there an efficient way to do that?

View 2 Replies View Related

Separate Data In Cell Into Columns

Jan 12, 2007

how to separate data such as this, into different columns?

85878; null;OMX;OM;2004-09-13 08:58:29.0;691.91;OMX Stock Index;693.01;688.67;691.91;15055;0.0;14.64;0.0;0.0;2004-09-10 00:00:00.0;0.0

Today this data is inserted into one cell, in one column, but in 50 000 rows (in 9 different sheets!). I would like to separe it into separe columns so I could run calculations on the data. Separating it manually would take me at least 1 year,

View 4 Replies View Related

Convert The Each Item [data] Above Into Separate Columns

Aug 9, 2009

I have a 45 page spreadsheet with over 3500 contacts and the data is currently listed in individual cells as seen below:

John Smith
Director of Business Travel Sales
ABC Hotel
1200 Market St.
Philadelphia, PA 19107
Phone (215) 555-1234
Fax (215) 555-4321

I want to convert the each item [data] above into separate columns so I can then save it as a CSV file and then export th data into an email list; but I have no idea how do to this.

View 9 Replies View Related

Converting Data In Two Columns Into A Row On Separate Sheet

Oct 20, 2011

I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.

Example - Sheet "Gp Trg Plan"
Column B Column D
Tower Module 1
Tower Module 2
Tower Module 3
Building Module 1
Building Module 8
Street Module 6

Ideally the row will look like.

Example - Sheet "Status WS"

Column A Column B Column C Column D Column E Column F Column G
Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date

There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.

View 4 Replies View Related

Separating Data From One Column Into 2 Separate Columns

Apr 10, 2013

I have a glossary with 400+ rows in column A. See examples below. I'd like to separate them so that the term is in column A and the definition is in column B. Once Done I will not need the . Normally instead of that tag I would have a hyphen separate the term from definition but the fact that some terms have a hyphen like "D-VHS" was throwing me further. In the end I will not need a separator because everything will be in separate cells.

DSL Digital Subscriber Line is a technology for bringing high-bandwidth information to homes and small businesses over ordinary copper telephone lines. A DSL line can carry both data and voice signals and the data part of the line is continuously connected.

D-VHS Connects a digital audio/video cable for use with some D-VHS digital VCRs.

E-Mail Messages sent to a customer's receiver that are viewed on their television screen. Pending messages are indicated by an icon on the on-screen status display (channel marker) and by having the Power button flash. (Not all receivers have a blinking light.)

View 4 Replies View Related

Separate Letters And Numbers In A String Into Separate Columns

Nov 20, 2012

I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.

The device label extraction is similar to this:

I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A

View 5 Replies View Related

Separate Numeric / Text Combination Into Two Separate Columns

Oct 9, 2013

How can I separate the following numeric/text combination into two (2) separate columns in Excel?


The result would be:

302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC

View 6 Replies View Related

Split Data Based On 3 Columns Into Separate Worksheets?

May 22, 2014

I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.

creating a macro or implicating one into the workbook to run for future reporting.

View 13 Replies View Related

Splitting A Long Column Of Data Into Separate Columns

May 21, 2008

i have a long column pair of data, each entry in its own cell:

10/5/20088:30:00 AM
10/5/20088:46:00 AM
10/5/20089:14:00 AM
10/5/200810:18:00 AM
10/5/200810:42:00 AM
11/5/20088:30:00 AM
11/5/20088:46:00 AM
11/5/20089:14:00 AM
11/5/200810:18:00 AM
11/5/200810:42:00 AM
12/5/20088:30:00 AM
12/5/20088:46:00 AM
12/5/20089:14:00 AM
12/5/200810:18:00 AM
12/5/200810:42:00 AM
13/5/20088:30:00 AM
13/5/20088:46:00 AM
13/5/20089:14:00 AM
13/5/200810:18:00 AM
13/5/200810:42:00 AM
14/5/20088:30:00 AM
14/5/20088:46:00 AM
14/5/20089:14:00 AM
14/5/200810:18:00 AM
14/5/200810:42:00 AM

how can i program a macro to 'split' this column according to date? please refer to the attached picture as an example. i know this is probably a simple question but please bear with me i'm still new to excel programming.

View 14 Replies View Related

Seeking Separate Data Into Columns For Specific Hour

Jun 18, 2013

I have repetitive task of copying and pasting. Macro to sort some data.

I have time values (sorted in order) in Column A and a value in Column B.

Sheet1  AB1TimeValue

I need separate the time each hour of data into columns. So the above would become

Sheet1  ABCDEF1TimeValueTimeValueTimeValue
200:00:00201:00:00402:00:001300:00:00201:00:00202:00:002400:00:001  02:00:003500:00:002  02:00:0046    02:00:0017    02:00:002

There are a different number of rows of data for each hour and sometimes there might not be any data for a specific hour.

View 4 Replies View Related

Look Up Data From Multiple Columns And Insert In One Cell Using Comma To Separate?

Jan 31, 2014

I have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:

The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.

Row 1 November
Row 2 1 5 15
Row 3 1 0 2
Row 4 0 1 3

November is in A3

If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2

View 14 Replies View Related

Reorganizing Data To Show Info From Two Separate Columns In Third New Column

Feb 5, 2014

I've got a problem with organizing my data. I've performed a study with several participants, each of which does several trials with 8 conditions (1,2,3,4,5,6,7, or 8), with each participant doing a condition more than once. I also have a separate column telling me whether they responded correctly or incorrectly (with a 1 or a 0).

I need to find a way to produce a new column to identify whether they got each condition (of the conditions 1,2,3,4,5,6,7, or 8) correct separately, i.e one column for responses to condition 1, one for condition 2 and so on....

It would also be useful if there was a way that once this is done I could summarize their accuracy of responses to each condition.

I've attached an example of my data. excel problem example.xlsx‎

View 14 Replies View Related

Excel - Parsing Numeric Data From Table Into Separate Columns

Nov 18, 2013

100% Bran Nabisco 70 130 10 5 6

AlI-Bran Kellogg 70 260 9 7 5

All-Bran w/Extra Fiber Kellogg 50 140 14 8 0

I am having trouble parsing the numeric data from the following table into separate columns. The five different numbers (ex. 70, 130,10,5,6 in the first row) represent nutritional info about the product. As you can see, the number length varies for the different categories. How to get these 5 numbers into their own columns using LEFT, RIGHT, MID, FIND, etc. functions.

View 3 Replies View Related

Converting Pipe Delimited Data String Into Separate Columns Using VBA Macro

Mar 6, 2014

I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.

Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date

View 2 Replies View Related

Count The # Of Rows In A Spreadsheet In Which There Is Non-blank Text Data In 2 Separate Columns

Dec 5, 2008

I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):

Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else

The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.

View 4 Replies View Related

Macro VBA For Copy Paste Columns Of Data From Three Tabs Into One Column On Separate Sheet

Nov 18, 2013

I have a workbook with four tabs or four sheets.

Tabs 1, 2 and 3 have a column of data (Column A) on each sheet. They all start from the same cell. Each cell of data are just numbers. The column (an array) of data will not have blanks between.

But, they are not the same number of data. They vary.

Meaning, Tab 1 may have 15 numbers (A1 to A15), Tab 2 may have 20 numbers (A1 to A20) and Tab 3 could have 5 numbers (A1 to A5). Each iteration of the workbook may have different number of data in the A column on these tabs.

Now on Tab 4, I want combine the data from all three tabs into one column (in column A).

So, Tab 4 has a column A with data from Tab 1 copy and pasted to (A1 to A15) as values, then (A16 to A35) have Tab 2 data copy pasted as values and (A36 to A40) have Tab 3 data copy pasted as values.

Basically, the macro on Tab 4 has to count the number of rows on each Tab that are populated with data values and figure out to copy all the data on Column A from each three tabs and paste the data value into the Tab 4 in one column of data (in values).

View 6 Replies View Related

How To Separate Row Data From One Workbook Into Separate Workbooks Based On Cell Data

Apr 23, 2014

I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.

View 9 Replies View Related

Filtering Columns When Adjacent Columns Are Blank

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


View 5 Replies View Related

Filtering Columns

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

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

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

Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)

Oct 13, 2008

I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

View 4 Replies View Related

Filtering Multiple Columns

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

Copyrights 2005-15, All rights reserved