Tracker
HOME    TRACKER    Excel

# Combining Cell Values?

## I'm having trouble combining some values in cells. For example in cell A1 i have the value 372-25. In cell B1 i want to make A1 part of another value (it is an APN if you must know). So that cell would be 011-372-25-11. I would be adding 011 as a prefix to each cell and 11 as an ending. I'm sure there must be an easy way of doing this. I have tried =011-A1-11 ="011-"A1"-11" ="011-A1-11"

Related Forum Messages:
Combining Two Values In Two Separate Cells To Make A Cell Reference Or Index Refer.
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:

A1 contains B
B1 contains 2
B2 contains "tribbles"

An imaginary function might go like this........

Combining Values In Multiple Lines
I receive a monthly report containing a list of people, and how much is being paid for certain services. The company that sends me this list is preparing to adjust their rates and it will be retroactive back a few months.

The way they plan on doing it is by means of taking a credit back several months, then "re-paying" the correct rate. The main data will include the month for which the payment (or credit) is being made, the person's unique identifier, as well as the amount.

Here's a sample of what it would look like:

Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, -20.00
John Doe, 123, 03012008, 25.00
John Doe, 123, 02012008, -20.00
John Doe, 123, 02012008, 25.00

So basically the above shows they paid \$25.00 (correct rate) for April 08, then they took back \$20.00 the prior two months (the old rate) and paid the correct rate right afterwards.

In what I need to do, this is going to be a lot of work. Is there a way to programatically merge the amounts given the member's unique ID as the "key field" as well as the same month? So it might look like the following:

Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, 5.00
John Doe, 123, 02012008, 5.00

Just giving the sum of the amounts for a the given people in the same month?

I'm pretty good with VBA but this one is stumping me.

Combining Date Field And Adding Values.
I have a spreadsheet that retrieves data from a 3rd party app/database. The data that is returned has two dates..

ie..
1/1/051.31.4
1/1/051.31.4
1/2/051.31.5
1/2/051.31.5
1/3/051.32.6
1/3/051.30
etc..

What I would like to see is..

1/1/051.32.8
1/2/051.33.0
1/3/051.32.6

Date range varies, but usually there are two dates retrieved. Date is pulled and display within A2:A700 Range.

Combining Cells Into One Cell
I am trying to combine multiple cells into 1 cell per row.

I would have "A" column empty, then combine "B", "C", and on into the "A" column.

Each cell value will be separated by a space or any special character I designate in the macro

I have attached an example which the output is separated by a space. Sheet1 has the original file and Sheet2 has the desired output.

Combining A Text Cell With A Number Cell
I am using excel 2007 and have the following information:

Cell A1: L6212- (formatted as text)

Cell B1: 05.50 (formatted as custom number 00,00)

When I merge the 2 cells using the concatenate formula I end up with

Cell C1: L6212-5.5

What I want is L6212-05.50

Combining Formulas In A Cell And Concatenate
I am trying to combine this formula with another formula but I cant get it right.
I have multiple worksheets that feed into 1 so I am using this formula to pull in the info.

=II.MissionCritical!C20. This info is a title like "Writing".
=II.MissionCritical!C23. This info is a yes or no.

So I need the =II.MissionCritical!C20 formula to also say if c23 is no add an *** to the front of the title. so the title would look like "***Writing". and if C23 is yes leave the title as is.

Combining SUM With Fixing A Cell Value
how can I combine the Formula =(SUM(A2:A100))/(SUM(B2:B100)) with setting a value of the cell D2?

What I want would look like this (in column C):

=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=0
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=100
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=200
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=300

and so on.

The point is that the values A2 to A100 are dependent on the input value in D2.

Combining A String To Reference A Cell
I'm trying to switch my vlookups to something more dynamic. I keep adding columns to the data sheet (sheet1 for these purposes) and so I have to go back and update all of the other sheets that use the vlookup. (I'll just give an example as sheet2 for these purposes)

So what I have currently:
=VLOOKUP(A5,Data_All,4,FALSE)
Basically the 'A' column in sheet2 has an integer value, and so the vlookup takes that integer and looks up on sheet1 which row corresponds to that integer and then returns that string value.

What I want:
='AO_Cat_' & 'A5'

Basically I have named every cell in sheet1 and the particular value that I want to pull is named "AO_Cat_%name" with the last part being the integer value. I don't know if that make sense or not. For example, on sheet2, A5 might be '16' and so the value that I want to pull is "AO_Cat_16"

If I were to do this from the VBA side of things, then it'd be a simple range("AO_Cat_" & id_number) where id_number was the lookup value. However, I'd prefer to have this directly as a worksheet formula for simplicity purposes.

Determining Larger Value Cell & Combining With Another C
I have 5 columns AS-AW. Column AS contains a letter (either A, B, C, D, E, F, or G) which is generated by a VLookup formula. Columns AT-AW contain numbers (from 1 to 6) generated by a conditional formula. I need column AX to combine column AS with the smallest of columns AT-AW. Is this possible?

Combining HTML Hyperlinks And Cell Contents
A list of stock symbols runs down one column. In the next column runs a list of the corresponding companies.

The companies are linked in the following fashion: http://www.smallcapinvestor.com/quotes?symbol=ug

For the above example, "ug" is the stock symbol for the company United Guardian.

My question:
Is there a way to insert the text of a cell into a hyperlink? For example, how would I achieve this logic in Excel: http://www.smallcapinvestor.com/quotes?symbol=[CELL CONTENTS HERE]

Combining Fixed Text In A Single Cell
I have about 20 choices I'd like a user to select from and have the text output to a single cell.

Just create a Data Validation List? Yeah, but that only allows you to select ONE of the items in the list -- I want to select as few as zero and as many as all 20, combining the resulting text into a single string - space or comma delimited.

My first thought was checkboxes, but all I can seem to figure out is how to get ONE of the results into the cell, meaning I'm better off with the Data Validation List because it's easier.

Especially since I need to repeat this for as many as 200 or 300 lines,

Combining Text And Cell Reference With Indirect
I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")

But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.

Combining Cells Based On Value Of Adjacent Cell.
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks.
Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names.

A B
1 Car,
1 Taxi,
2 Bus,
2 Lorry,
3 Skateboard,
3 Bike,
1 Motorcycle,

What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode

Combining Custom Formated Date With Text In New Cell
For making quotes at work, I'm looking to have the quotes named automatically. The format of naming our quotes is the date (in this format) then quote name: yymmddNAME (Example for today: 090720EXCEL)

I'm using the =NOW() function for the date (Cell A1), then doing a custom format to turn the format into yymmdd then in a different cell I'm putting in the "NAME" (Cell A2) and what I want to do is combine the 2 cells (Cell A3) by doing: =A1 & A2
but when i do this, the format of the date gets messed up.

Combining Names: If The Cell Is Blank The Result Should Not Leave A Space
I have 6 columns and would like to combine them. If the cell is blank the result should not leave a space.

- Prefix
- First Name
- Last Name
- Middle Initial
- Last Name
- Suffix

Example: Mr. Henry J. Weeks, III
Example: Henry Weeks
Example: Mr. Weeks
Example: Henry J. Weeks, III

Combining/merging Multiple Rows From One Worksheet Into One Cell In Another Worksheet
I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.

This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.

I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.

I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.

Here's an example of what i'm working with,...

Range is C40:D48

Combining MAX, ROUNDUP &amp; IF For The Same Cell
I am trying to create one formula (in one cell) so that it performs the following three things:

1) Rounds any number to the nearest dollar

2) Makes the minimum dollar value \$2.00

3) If another cell (G2) reaches \$100.00 or more, I want it to revert to another cell's (B2) value.

So, in essence, I want to combine the following two things:

=MAX(2,ROUNDUP(F2,0))
AND
=IF(G2>="100",B2)

I do not know how to correctly order these so that it performs all three things I'm looking for it to do. I've posted this before, but was given an answer that created a circular reference and did not make the minimum value \$2.00.

Find Same Values In Different And Unequal Cell Ranges And Refer To Values
I have data similar as shown in the following:

A1A11
B2AA1122
C3B22
D4C33
D44

The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this

A111
AA1122
B222
C333
D444

the third column always will have at least the same letters as the first column, but new letters/entries can occur.

Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)
If I have, in one cell (call it D1):

EH,DR,HU

and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H

Recalculate Cell Values To New Values
I am trying to write code that runs a calculation on each cell in a range and replaces the current value with the calculated one. The calculation is very straightforward (see green range in example file)- instead of the absolute value in a cell (yellow range) it displays the ratio of said cell value to a value from another column (blue range). Basically, after running the macro, the green cells (just formatted for ease of reference) would replace the yellow ones.

Combining IF And IS
I want to see if a goal has been met and get a "Yes" or "No" so I wrote
=If(A1>=A2),"Yes","No"

But on months not worked where goal and production both equal 0 then I am getting a return of Yes. How can I say is A1 >= to A2, but if both are 0 return N/A?

Combining AND And OR
I am having problems combining AND and OR into a single statement. To start I have a statement =IF(AND(B978="P",R978=0,S978=0),"processed",""). If B=P and R=blank and S=blank then display PROCEED else make blank.

What I want to do is check if S is blank or "N" but when I try to add an OR I get error messages regarding missings )'s =IF(AND(B978="P",R978=0,OR(S978=0,S978="N"),"processed","").

Combining IF, AND, OR.....
I have some code where I use and statements, as follows:

Combining If And Lookup
I need to find a formula to show the time allowed for each person, now they recomend combining 'if' with 'lookup'. now I may be having a blonde moment but I have tried to figure it out for a while now and i am stuck.

Combining 3 Macros
I would like to combine the following 3 macros into one macro. Try as I might, I can not figure out how to do it?

Sub DeleteEmptyRows()

Dim Counter
Dim i As Integer

Counter = 5000
ActiveCell.Select
For i = 1 To Counter
If ActiveCell = "" Then
Selection.EntireRow.Delete
Counter = Counter - 1
Else
ActiveCell.Offset(1, 0).Select
End If...................................

Combining The Rows
I've attached a spreadsheet with a simplified example of my situation. Notes appear at the far right to explain my desired result. I've read through lots of posts on the forum but haven't yet found anything to mimic.

I'm building a calendar to display promotion activity by week for multiple customers and multiple products. The source data only lists one event per row, so the current result is not as concise I know it could be. I end up with twleve different rows for customers with activity each month on the same product, where I'd like to see just one row with all activity for the year.

Can anyone assist with either VBA or a formulaic solution? I've tried a few things with if/and statements followed by a concatenation of previous rows, but this seems like it will not have the intelligence to calculate only rows that match, I keep coming up short. Can OFFEST be used in some way?

Combining 3 Or If Then Statements
I am trying to combine all 3 of these statements in a formula and the syntax seems to keep tripping me up:

=IF(AND(G27=" ", M23=0), " ")

=IF(AND(G28=" ", M23>0), F28+\$M\$23, " ")

=IF(G29=" ", " ", IF(G29>0, M23>0, G29+M23))

Basically, If M23 = 0, I need the cell to remain blank. But if M23>0 then the 2nd and 3rd formulas need to apply.

Combining Two Or More Cells
I know a simple formula would 'almost' do what i want...but i was hoping for a simple feature....

i have several text cells that i want to combine into one cell.....

so a formula like =A1&B1&C1.... would work fine....but is there anyway to present this data a little fancier? for example separating each cell entry after a "break" or something....

Combining COUNTIF, RIGHT, <
Excel 2007

I am trying to count how many cells have the last 2 digits of 84 or less. I tried this formula, but it is not working.

=COUNTIF(RIGHT(H4:H129,2),"

Combining Columns
I have monthly reports that come to us from a Data Warehouse. They vary in size(row length). The problem is that the information in one column actually belongs in the next column (i.e. C to D). As this information in Column C is just an "offset" from Column D there is no issue with overwriting any data in column D. Manually, I could copy the columm and Paste Special, Skip Blanks, to column D.

I've been trying to do this in code as some reports can be fairly lengthy: As there are some blank rows, I've inserted this FinalRow procedure, to provide the size of each report:

FinalRow = ActiveSheet.UsedRange.Rows.Count + _
ActiveSheet.UsedRange.Rows(1).Row - 1

However, in trying to use the FinalRow value in a "Copy the contents from Column C....Cells(20,3 to FinalRow, 3) and then PasteSpecial to Column D (Operation:=xlNone, SkipBlanks:=True) has not been productive. It keeps producing a number of Errors, in every way that I have tried to make this work.

Combining Code..?
I have two different bits of code as follows:

Combining Records Together
I have a problem in excel that is very urgent to resolve. I have a table that contains duplicate records e.g two people living at same address. I want to merge those records whose address field value is same. Find attached an excel sheet that contains the exact data and the exact output that I want. I know it requires VBA coding.

Combining Two Columns Into One
Trying to combine Column A (First Name) with Column B (Last Name) Probably very simple.!

Combining First & Last Name
I have two seperate columns, First Name (Column B) and Last Name (Column C). Is there a way a macro can combine the two into one cell Last Name, First Name.

Combining Two Formulas Into One
I have the following formula... in Cell T4 =IF(\$Q4="",(M4&(IF(L4="","",(" ("&L4&")")))),(IF( ISNA(VLOOKUP(\$Q4, Lookup!\$A\$1:\$B\$5,2,FALSE)),Q4,VLOOKUP(\$Q4,Lookup!\$A\$1:\$B\$5,2,FALSE)))) But I also want to replace the 'R(space)' in the cell at the same time. I currently do that in a separate cell using: =SUBSTITUTE(T4,"R ",""). How do I combine the two..so that it is done in one action ??

Paste Values From Another Worksheet (paste Special, Values) In A Cell Which Is Lookup Value I Get #N/A
I have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?

Combining Rows Containing Text
I have 2 spreadsheets with a total of 100,000 rows of item descriptions. The problem is that I have to combine the text in the rows to create a complete item description into one cell. The number of rows for each description varies from 1 row to 7 rows. Excel experts here are using the term "concatinate."

There is one common element to the item description. There is a unique 11 digit code (in its own separate column) in the appropriate number of rows for each description. Note that the unique # I identified below doesn't look right since this document does not allow me to show the numbers in a column.

The spreadsheet looks like the following (I have created 2 item descriptions). I have at least 40,000 more.

PAD, EASEL, 27" X 34", ERASABLE, STATIC 61562030632
AVERY #24-391 OR EQUAL 61562030632
PAD, WRITING, 8.5 X 11.75, LEGAL RULE, 61562030940
PERFORATED 61562030940
RMOD FOR COLOR AND TO COMPLETE CATALOG 61562030940
NUMBER 61562030940

I could use a formula or a macro that would combine the descriptions into one cell. One other problem was identified as I tried to combine the text. There has to be a space created after the last letter of each row, otherwise, the last word in the row combines with the first word of the next row.

Combining Two Macros To Run As One
I have two macros that I need to run as one and I am not sure how to call the second macro from within the first. I'm sure it's easy...If you know how... Thank you in advance.
--------------------------------------
macro 1
Sub PrintUsedRange()
'
Dim ws As Worksheet
'
For Each ws In Worksheets
Application.CutCopyMode = False
With ws.PageSetup
.Zoom = False
.CenterHorizontally = True
.Orientation = xlPortrait
.FitToPagesWide = False
.FitToPagesTall = 1
End With
Next ws
ActiveWorkbook.PrintOut............

Combining Specific Rows
I have received a 'data dump' from a local county assessor. It is formatted for their use in displaying on their website. This particular file associates a parcel number with the legal description. However, with longer legal descriptions, they have replicated the parcel number and continued the description. The fields in the sample I have attached include (A) as the parcel number (which is common through all the files), (B) a county id, (C) the order that this specific portion of the legal description shoud be, and (D) the legal description, itself. In the sample you will see one specific parcel number that repeats itself more than 20 times, while other parcel numbers only appear once. What I hope to do is have one row for each parcel number with no replications. I hope for (A) be the parcel number, (B) be the county id, and (C) be the combined legal description. There should be approximately 275,000 unique parcel numbers.

A second option would be to move the lower continuations up, into the same row. For the example above refering to the 20 rows of legal description, I can work with 1 row and 20 columns(E through Z).

The provided coding was perfect, my problems were found to be the environment I was working. Once the data was prepared properly, the coding worked very well.

Combining Multiple Templates Into One
A member of my team has created numerous templates in Excel 2007 for various customers. Each template has some columns that are identical for each (Cust Name, for example), and some columns are unique to each customer. I want to combine all of these individual templates into one Excel workbook and be able to click (select from a dropdown list) the customer name, and have only those columns that pertain to that customer appear. Select another customer name, different columns appear, and so on. Is this possible to do in Excel 2007? [This is my first post, so please forgive any offenses if my question is too long.]

Combining Index And Hlookup?
I have created and named a table with salaries based on years of experience and education.

I am having trouble writing a function to search for a specific amount and result in the the cell below that specific amount.

So people are moving up by one year on the schedule I would like to use their current salary to lookup and display their salary for next year.

I am confused on how to incorporate the different functions I am assuming an index and a hlookup...

Combining =REPT And =INT
ok - I have numbers that need to be converted to 12-digit numbers with leading zeros if they are less than twelve digits. for example, 1234567 would turn into 000001234567 to have 12 digits. to do this, i use:

=rept(0,12-LEN(A1))&A1

additionally, i need to strip off the last three digits and replace them with three zeros. my example would now become 000001234000. assuming the result of my first formula (above) is in cell B1, i would use:

=INT(B1/1000)&"000"

Is there a way to combine these two functions into one formula to make this conversion process more painless? Or is there another formula/function I can use that I haven't thought of or do not know?

Combining Data To Sheet3
I have been trying to work between Access and Excel and my forehead is getting bruised from the brick wall in front of my desk. I am finding Excel much more useful, but request some help with the final tasks.

Column1 of Sheet1 is an identifer. This number, in some cases is repeated which is necessary for when I include this data back into the master sheet. I make note of identifing this as 'text' as to not lose any zeros or go 'scientific'.

Column1 of Sheet2 is the same identifer but does not repeat (hence fewer rows). Column2 of Sheet2 is the city name which the identifer in Column1 is located.

I need Column1 Sheet1 repeated on Column1 Sheet3 and I need the respective city name from Column2 Sheet2 in Column2 Sheet3. Once I have this I can re-insert it into the master sheet.

The two areas I am trying to learn is if I am to use an If or a Findit, and secondly incorporating the multiple sheets.

I have attached a sample file.

Excel 2007

VBA Combining Worksheets ...
Four worksheets (A,B,C,D) all begin with row2 and end with the last non-blank cell.

What VBA code would merge the information from the four worksheets to Worksheet E?

Combining And Merging Data
Hi i need to combine and merge difefrent data into groupings. EXAMPLE

1ax
1bx
1cx
2ax
2bx
2cx

It needs to end up as
1ax
2b
c
With a 2 or 3 empty lines until the next set of data
But if for example i have this:

1ax
1bx
1cx
2ax
2bx
2cz
3 c z......................

Worksheet Combining Macro
I'm trying to write a macro that selects file A, copy some particular columns in file A, copy them into a new workbook, then repeat the process for file B, and copy and paste the columns into the same working workbook. Does anyone have any suggestions on using any one of the workbook, worksheet functions to do so?

Combining COUNTIF And AND Functions
The format for combining COUNTIF and AND in a
single function - I can't seem to get the format right.

What I want to do is COUNTIF range 'Issued_by' = A8 AND range
'Period_all' = 1

Combining VLOOKUP Formula
I have a set of data consisting of 3 columns.

Column A - Clinic Names (Alpha Numeric)

Column B - Task (Alpha Numeric)

Column C - Output (Numeric)

There are 52 clinics and each clinic has to perform the same 36 tasks

example:

I would like to seach for a clinic then search for a particular task and then display the output

I tried a Vlookup with a nested Match and that did not work.

Combining Multpile IFs
Individually these formulas work - is there anyway I can combine them into one formula in one cell so the will look at the month and then perform SUM function?

=IF(I\$2="apr",SUM(SUMIF('Raw Data'!\$A\$1:\$A\$64773,\$A\$1,'Raw Data'!D1:D64773),SUMIF('Raw Data'!\$A\$1:\$A\$64773,\$A\$74,'Raw Data'!D1:D64773),SUMIF('Raw Data'!\$A\$1:\$A\$64773,\$A\$75,'Raw Data'!D1:D64773),SUMIF('Raw Data'!\$A\$1:\$A\$64773,\$A\$76,'Raw Data'!D1:D64773),SUMIF('Raw Data'!\$A\$1:\$A\$64773,\$A\$77,'Raw Data'!D1:D64773),SUMIF('Raw Data'!\$A\$1:\$A\$64773,\$A\$78,'Raw Data'!D1:D64773)),"X")

Etc for all 12 months?