If Multiple Conditions Exist Then Combine Text Fields?

Apr 1, 2014

I am trying to build a list of account numbers based on conditions in a spreadsheet. Every account number will look like: XXXXXX-XXX-XXX, depending on if there is a 'Y' in both fields...see attached. I am desperate to get this done and have hit a complete road block.

View 2 Replies


ADVERTISEMENT

Combine Data From Multiple Sheets With Conditions

Dec 27, 2012

What I would like to do is combine all data (from sheet: Page1,Page2, and Page3) into one sheet named 'Consolidated'. Unfortunately, as you will see from the attached file, my programming and understanding of VBA programming is pretty naive.

What I am after is, when I combine the data, from all sheets into the one, the data should be

1. Automatically Sorted by Patient Name

2. Group, results of the same patient from all sheets one below the other in seperate rows

3. Delete any other rows that may have a patient name and ID, but rest of the rows (New Mole,Clinically Suspicious......Benign Naevus) are blank. (This happens because of the way I have designed my userform. For first visit the details are entered in Page 1, then for second Visit Details are entered in Page2, and for third visit in Page 3. So when I enter the first visit details, the Patient Name and Patient Number are copied into Page 2 and Page 3 even though the other details remain blank. I have done it this way because, if say the Patient comes for visit the second time, the clinician may or may not be aware which visit this would be for the patient. So as soon as they enter patient number, it will give them a message that Patient already exists. So when they click Open Existing Patient Record, the form autopopulates with any notes from their previous visit into the respective page (i.e Page 1,Page 2 or Page 3).

Page 1 (Page 2 and Page 3 are exactly the same)
Patient Name
Patient Number
New Mole
Clinically suspicious
Changed from mapping Photo
Level of Suspicion: Score
Monitor

[code].....

View 3 Replies View Related

Count With Conditions & Doesn't Exist In List

Jun 4, 2008

I have a Sumproduct formula to count instances of a particular event (from a list of events) based on multiple criteria.

I am trying to utilize the same method to count instances of all events not defined in the list of events but I would welcome any solution

In the attachment,

Defined list of events A4;A5 (this is just an example, the actual list is approx 100 events)

Data being counted F2:N10 (actual data approx 1000 rows)

My working formula is in cells B4 through D5

My not working attempt to adapt the formula B6

View 3 Replies View Related

Combine Data In Rows For Certain Fields Only

Aug 8, 2008

I need to combine data from multiple rows to single rows, for certain fields only. The database is large and can described as having an ID column, followed by columns 2 to n that do not need to be combined, followed by columns n+1 to N that do.

I've attached a sample dataset along with the desired outcome. For the purposes of illustration the sample uses n=3 and N=5 but the actual dataset has much larger dimensions.

View 9 Replies View Related

Using Lookup Function To Combine Text From Multiple Rows

Oct 4, 2012

At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..

Sheet 1
Row 1: Mike │ "Great to work with"
Row 2: Mike │ "Bad manager"
Row 3: Tom │ "Great guy"
Row 4: Mike │ (blank)
Row 5: John │ "Cool"
Row 6: Mike │ "Best boss"

On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.

Sheet 2

="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss

View 6 Replies View Related

Counting Multiple Text Conditions

Nov 7, 2008

I am working on a spreadsheet that contains multiple text conditions that I need to keep count of. I would like to try the countif but I don't know if this will work. Here's a summary of my spreadsheet

Column F in my spreadsheet contains 7 different regions (all text).
Column D contains an open or closed option that is not validated.

In my totals section of the spread sheet I need to count each region that meets the "closed" criteria.

View 3 Replies View Related

Testing For Multiple Text Conditions Within Rows

May 13, 2009

I have an excel template that is used for communicating in projects to teams their responsibilities and defining which stream/group has Accountability/Responsibility/Consulted/Informed (known as a RACI model) for the activity.

For any given project activity, there are rules:

One person is responsible for performing the activity (R)
Only one person can be accountable (A)
Many people can be consulted through the activity (C)
Many people can be informed of progress (I)

You must have an A and and an R for each activity
And one person can be accountable AND responsible A/R

Given that project activities can run to 100 + activities in some of our more complex programmes.... I'm looking to provide a flag in the spreadsheet for when an error has occurred and a gap in Responsibility or Accountability is created. (Less worried about testing for C's and I's)

See attached, I hope it is clear.

View 7 Replies View Related

If Formula To Combine Two Lookup Conditions?

May 23, 2014

Combine two lookup/IF formulas:

[Code]......

So if formula 1 conditions aren't met then it by default would result in formula 2 conditions being met.

View 5 Replies View Related

Combine 2 Rows Of Data If Conditions Are Met

Jan 17, 2013

combining 2 rows of data if certain conditions are met. I have attached an example of my worksheet for reference.

Example.xlsx

What I want to happen is if a number in column J is followed by an "x" then the values in columns AQ and AR should be combined with the row of the same number (e.g. if 236x is in column J2 then the values in AQ2 and AR2 should be combined with AQ3 and AR3 because 236 appears in column J3, the same process needs to be repeated for 237x with 237 etc.).

View 3 Replies View Related

Combine And Sum Rows Of Data In Excel Dependent On 3 Conditions?

Jan 3, 2014

I have a dataset that, due to the nature of how it is generated, separates certain orders into separate rows. Which orders these are is irrelevant, I just need a quick and easy way to make excel re-combine these rows in to one entry, and sum up the data in one particular column in the process of doing this.

The separate entries of a single order can be identified by the data in 3 columns matching - I have attached an example.

The constituent parts of the same order can be identified when the country, city, and code columns all have matching data as you read down. E.g., where

Country City Code
a 1 12345
a 1 12345
a 1 12345

Where this is the case, I want excel to automatically sum the count of shops for this order and then delete all but one of these rows, with the remaining row now containing this summed figure. Failing this, a new worksheet being generated/a new column giving me this data would be good.

View 6 Replies View Related

Return Value If Text Exist In A Range

Sep 27, 2011

Here is a sample data, i am searching for text "home" against every airline among all countries and want to allocate home country to each airline (which is top row). Is there any formula which can do this?

AE - United_arab_emiratesAF - AfganistanAL - AlbaniaAM - ArmeniaAA - AMERICAN AIRLINES
AwayAwayAwayAwayAAZ - AMERICAN AIRLINES C/S AA NON OAwayAwayAwayAway
AB - AIR BERLIN GMBHAwayAwayAwayAwayABZ - AIR BERLIN PLC C/S NON AIR BERAwayAwayAwayAway

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

View 2 Replies View Related

Delete Text That Doesn't Exist

Jun 10, 2008

In the attached sheet, i would want to delete the text "ABC" because it is not contained anywhere in column A.

Couldnt find a macro on here for this...

View 6 Replies View Related

Return Values From Multiple Column If Exist

Sep 20, 2013

I have a challenge which I am trying to solve without VBA and I am uncertain how to approach

If I have the following spreadsheet consisting of three columns:

FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
D.........................
E.........................
F........6........4......

The result I would like to achieve is

FieldA, FieldB, FieldC
A........3................
B........5........6......
C.................7......
F........6........4......

So, it means I would like to return the rows where Columns B and C have a value. Is this possible with strictly formulas?

View 8 Replies View Related

Populating Fields In Multiple Cells Based On Criteria In Multiple Columns?

Feb 24, 2014

Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.

In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.

CURRENT SHEET

ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....

The attached example file may be a better alternative to view this problem.

View 2 Replies View Related

Change Text Color When Comparing 2 Text Fields

Aug 20, 2013

How would I compare two text fields (old and new) and change the font in the 3rd column (Difference)?

For example:

Old: I can not figure 3 things out.
New: I can't figure 2 things out.
Difference: can not can't figure 3 2 things out.

View 1 Replies View Related

Filter Data - Check If Multiple Sheets Exist

Feb 28, 2013

Using VBA, I am running code that filters the data in my column "Account" on my main sheet, creates a new sheet for each account in that column then copies the filtered data to the new sheet. There will always be the same list of Accounts with the exception that there might not be data for one or more.

In that case, I still need it to create a new sheet with the account name but just have some text stating "no data available for his account".

I can find code to search if a specific sheet name exists but, in my case, I don't know which one won't have data at run time.

I was hoping I could put a static list of the expected sheet names into an array and loop through that list and create the sheet for any that it can't find.

My list: FTL, DTB, CAR, BLD, RSG, STS

View 2 Replies View Related

Programming Multiple Inputs With Multiple Data Fields

Feb 19, 2009

I am trying to set up a "diet log" for my nephew to track his eating habits for a school project. I am trying to make the "total" section of the attached spread sheet auto calculating depending on the information placed in the "breakfast / lunch / dinner / snack" catagories. I have two worksheets in the file. The first being the preferred method of a single cell and seperating the items using only a ",". The second sheet has a little bit different lay out which I don't think will work as nicely.

Honestly, I am not sure if this will even be possible, but I figured I would try here as a final resort. Currently there are just some sample foods in there as I will let him fill in the actual workbook for his use.

View 7 Replies View Related

Multiple Data Validation Across Multiple Fields?

Jul 23, 2012

I have a spreadsheet setup like a timesheet: Employee Name, Classification, Rate, etc.

I setup the Employee Name field as a drop-down. Since there are numerous employees with different rates, I want to just select a name so that the other fields fill-in with the appropriate rates.

View 1 Replies View Related

How To Combine Text Values Of Formulas In Addition To Adding Text

Jun 15, 2013

I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.

Here is the data that I am trying to combine

C14: 2013
C15: Period 6
C16: Week 1

In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"

Is there such a way to do this?

View 9 Replies View Related

Combine Cell Content With Text File & Save As Text

Feb 21, 2008

- I have excel file with data I need
- I have fixed txt(html) template that i need to integrate Excel information into
- Final result that I want to achieve is saved .txt(html) file with combination of fixed information (text) and data from excel cells.

I need to writing a VBA code for each of above (integrating text & cells, saving results as text)

View 5 Replies View Related

Combine Workbooks With Multiple Sheets Into 1 Multiple Sheet Workbook

Mar 16, 2008

I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.

View 9 Replies View Related

Remove Modules In Multiple Open Files Results In Error If Module Doesn't Exist

Jan 30, 2008

I have a number of similar templates on a server used to produce quotations from other files with lookup formulas. They all have 2 modules, 3 & 4. Module 3 deletes certain data and shows values instead of formulas for most of the pricing etc. Module 4 contains a macro that logs info in another central workbook on the server.

If 2 files are opened based on the same template at once, when the Quote_Wrapup macro (in module3) is run on one of the open files(code follows) from a button on the spreadsheet it often produces a Run-time error '9' Subscript out of range error.

Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("D8:E9").ClearContents
Range("D8:F9").Interior.ColorIndex = xlNone
Range("qdata5").Font.ColorIndex = 2
Range("qdata6").Font.ColorIndex = 2
Range("A18:A1018").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value
Range("A980") = Range("A980").Value...................

View 8 Replies View Related

Not Displaying Text Across Fields?

Jan 19, 2013

I'm using an Excel spreadsheet as a database (mostly text based), so that in columns 1 and 2 are names and in column 3 is notes. I'm keeping all the columns single height (ie: not wrapping text). Where there is nothing in column 4 onwards, the text from column 3 displays across the subsequent blank fields.

View 2 Replies View Related

Mining Text Fields

Mar 23, 2009

How would I group and count the occurences of specific words in a text field? This is my first step in trying to develop a scoring method based on our notes.

If the sentences above were in the text field, I'd expect the words "a" and "in" to come back with a count of two while all other words would come back with a count of 1.

View 9 Replies View Related

Replace Text In Fields

Jul 21, 2006

I would like to create a very simple Excel file that makes the following:

If I enter 2 in a field I want that 2 replaced with "07.15-19.00" and also a 1 entered to the field next to it. If I enter a 4 I want the 4 replaced with 00:00-08:00 and the number 8 entered in the field next to it.

View 8 Replies View Related

Using The Same Header In Multiple Fields

Apr 25, 2014

So, I've created a pivot table and need to use one of my values in both the row and value fields. I haven't seen a way to easily do that so far. Is there something I'm missing or is this not possible with Excel (yet)?

View 3 Replies View Related

Searching Multiple Fields

Sep 27, 2009

I am fillin out a spread sheed with a num ber of codes. I want to create a excel formula that will look at the entire column and search for each of the codes. If I have forgotten to add one of the codes into the column I need the formula to let me know.

What I was thinking might work is this...

To look for one of the codes I would do this.
=IF(MATCH("FLA",E5:E36,0)>0,TRUE,FALSE)

This works great if the Value is in there. If it is not in there I get #N/A instead of FALSE. Is there a way to get the MATCH to return another value if it is not found in the range?

Is there an easier way to look for multiple values? So far the only solution I have found is like this

=IF(LEFT(Q19,1)="D",7.5,IF(LEFT(Q19,3)="FLB",7.5,0))

For each value I need to look up I add another IF function. I have about 17 different values I need to look for. This makes for a ugly function but it seems to work.

View 9 Replies View Related

Vlookup On Text Fields Not Working

May 12, 2014

I use Vlookup all the time but this is one has be stumped. On the attached spreadsheet I trying to lookup the description on sheet 2 (lookup data) and return it to description on sheet 1 (orig data). I have the both fields formatted as text so I'm not sure why it's not returning the value?

View 5 Replies View Related

Extracting Text Fields From A Txt File

Jan 24, 2010

I have a file from PubMed with a bunch of citations in it, which often go above 10,000. There are fields that mean certain things and I would like to be able to import the text file into Excel and parse certain fields into columns.

The text file looks like this:

PMID- 19782657
OWN - NLM
STAT- In-Process
DA - 20091109
IS - 1872-6240 (Electronic)
VI - 1303 .......

View 9 Replies View Related

Limit # Of Fields In Text Import

Jul 18, 2006

I have a space delimited text file with 3 columns. The 3rd column is a string which may or may not have spaces in it.

How do I import the text file so that I end up with 3 columns in Excel and still have the entire 3rd string from each line?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved