Combining Cell Values?

Nov 26, 2008

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"

ADVERTISEMENT

Combining Cell Values In Column Based On Other Cell Values?

Mar 2, 2012

it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.

View 5 Replies View Related

Combining Cell Values?

Feb 8, 2012

I have the follwoing example. Basically, there is an "X" for each posession of Andrew (cells: B1, C2, D3, E4). What I'm trying to do, in another sheet, is centralize the below information in only one row for "Andrew", with an "X" under each of his posessions (all the 4 "X"-es are now in cells B1, C1, D1, E1).

CarPhoneHouseBoatAndrewXAndrewXAndrewXAndrewX

View 2 Replies View Related

Combining 2 Cell Values - Without Losing Data

Dec 4, 2013

I have a data set that I'm trying to sort in order to be efficient for some of my team members.

Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive.

Col A

123-45678-A-1
123-45678-A-10
123-45678-B-2

I need to eliminate the letter from the data, add a leading zero to all single digit numbers and sort ascendingly.

My first step is to split the data into columns using the "-" as a delimiter. I end up with 2 columns as shown below.

Col A
Col B
123-45678-
1
123-45678-
10
123-45678-
2

Next I add a leading zero to Col B. The assumption is there will never be more than 99 numbers, so I use the following code:

Columns("B:B").Select
Selection.NumberFormat = "00"

To produce:

Col A
Col B
123-45678-
01
123-45678-
10
123-45678-
02

Now all I need to do is rejoin these 2 columns before sorting. My current code is:

Sub Rejoin_Container_Number()
x = 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Do While Cells(x, 2).Value ""
Cells(x, 1).Value = Cells(x, 2).Value & Cells(x, 3).Value

[Code]...

As many have probably already guessed, this produces the following result:

Col A
123-45678-1
123-45678-10
123-45678-2

I'm looking for:

Col A
123-45678-01
123-45678-10
123-45678-02

**zeroes in BOLD for reference only, result does not need to be bold**

View 4 Replies View Related

Combining Text And Cell Values From Multiple Worksheets

Dec 1, 2013

I have the below macro which is failing to insert text into A1 of the Header Sheet, followed by the values in the designated cells that are from Sheet1. I would also like to have the values in cells J2 and K2 enclosed in single quotes.

Sub Header()
Worksheets("Header").Activate
ActiveSheet.Cells(1, 1).Select
ActiveCell.Value = "create or replace" & " '" & Sheet1.Range("J2").Cell.Value & "' " & " '" Sheet1.Range("K2").Cell.Value & "' "
End Sub

how do I get it into the nicely formatted version most of you are using? The Mr Excel HTML add-in?

View 2 Replies View Related

Combining Two Values In Two Separate Cells To Make A Cell Reference Or Index Refer.

Jul 15, 2009

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........

View 3 Replies View Related

Combining Values In Bar Graph

Jul 9, 2012

The company I work for has kept a list of the types of objects sold with their houses and how many were bought for each customer, via an Excel document. Here's a snapshot - You can see the name of each type of object on the left, with each column afterward representing an individual customer (Each vertical row is one customer) and how many of each item they bought.

[URL]

We're hoping to make a bar graph of how many of each type of item has been sold. I selected all the titles and columns of numbers and ended up with this, which is almost what we're looking for:

[URL]

The problem here is that each individual customer has their own line on the graph. The Recessed Can Light one I highlighted is a good example of a lot of people who bought varying amounts of that one item. What I'm looking to do is combine the individual sales for each item and make a single bar for each representing the total sales overall, so we can gauge what the best and worst-selling items are. Is there any way to do this?

View 4 Replies View Related

Combining Separate RGB Values

Mar 13, 2013

I have a User Form in which users can enter R,G & B values in 3 seperate Text Boxes in order to change the colour scheme of a worksheet to suit their personal taste.

I have tried combining the 3 values into a string to give, for example, RGB(255, 182, 45)

But, of course, because it is a string variable it is enclosed in quotaion marks and so, when add to a .Interior.Color function, the qutation marks are added too and the macro fails.

I have tried altering with Left, Right, Mid and Trim but cannot get rid of the quotation marks. I also tried converting to Hex but got totally confused there!

code to convert the values in the 3 list boxes (called LBRed, LBGrn and LBBlu) into an RGB value that can be appended to .Interior.Color

View 2 Replies View Related

Combining Two Area Charts With Different X And Y-values?

May 10, 2014

I need to have two area charts combined, but they have different x and y values. Is there another way to do this.When I try to combine them in one graph, I can only use one x-axis value.

In the excel sheet the PV needs to be the x-axis.

View 1 Replies View Related

Combining Values In Multiple Lines

Apr 15, 2008

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.

View 9 Replies View Related

Combining Values To Create 2 Digit Pair?

Jan 17, 2013

In A1
01234

In B1:k1
01 02 03 04 12 13 14 23 24 34

I am looking for a formula that will combine each digit together as a 2 digit value

Is this even posible?

The value in A1 could range from a 3 to 9 digit value.

View 3 Replies View Related

Combining Date Field And Adding Values.

Sep 21, 2006

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.

View 3 Replies View Related

Combining Some Of Rows Values Based On Multiple Criteria (2)

Apr 18, 2014

I have a table based on transport numbers who sometimes have doubles. Example:

Number Postcode Value1 Value 2...
106200 8500 10
106200 8500 5
106200 8600 6
106201 5500 4
106202 4000 1
106202 4000 1

So it works as following:

A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine

So shortly:
Transportnumber double/triple...+ Postcode double/triple...= combine these rows.

if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.

So example of end result for 106200 would be
106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to)
106200 8600 value ( this was unique so it stays unique)

View 4 Replies View Related

Combining Two Functions Into One Cell?

Jul 23, 2014

I am trying to combine the following two function into one cell. The second column contains a "space" before the number. The third column is the result. =CONCATENATE(Q2,",",+R2) and =SUBSTITUTE(K2," ","")

Here is a sample table with showing the data and result

4
5L
4,5L

5
6R
5,6R

5
7L
5,7L

6
10R
6,10R

6
3L
6,3L

View 1 Replies View Related

Combining 2 Formulas In 1 Cell?

Jan 13, 2014

I am looking at combining 2 formulas and have them in a single cell. Currently I am using this one =IFERROR(IF(S3-V3=0,"Completed within agreed time!",S3-V3),"") if the value =0 but need another one if there's no value in V3 it would say "Outstanding" I have tried to add IFBLANK to the existing formula but I must be doing something wrong as it doesn't work.

View 1 Replies View Related

Combining MAX, ROUNDUP & IF For The Same Cell

Feb 22, 2010

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.

View 7 Replies View Related

Combining Cells Into One Cell

Sep 17, 2009

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.

View 9 Replies View Related

Combining SUM With Fixing A Cell Value

Jun 20, 2008

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.

View 9 Replies View Related

Combining A Text Cell With A Number Cell

Dec 27, 2009

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

View 10 Replies View Related

Combining Numbers In A Column Into One Cell

Jan 14, 2014

Say I have a column of numbers. I'd like to be able to string them together in the following format: number1|number2|number3....

connect.xlsx

View 3 Replies View Related

Combining Formulas In A Cell And Concatenate

Feb 18, 2010

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.

View 2 Replies View Related

Combining 2 Rows That Share 1 Cell

Feb 27, 2013

combining 2 rows that share 1 cell so that it is easier to format cells. For example: A4 and A5 have been combined with B4 and B5 to form 1 cell that has content. How can I convert this to a cell that only occupies A4?

View 1 Replies View Related

Combining If And VLookup Formula In One Cell

Nov 19, 2013

I am trying to create one formula that can be copied to other cells to get varied results.

Here are the formulas

1. =IF('Calculated Rental Fees'!H4='Daily Rentals & Charges'!$B$28,'Daily Rentals & Charges'!$B$30,VLOOKUP('Daily Rentals & Charges'!$A$20,LATECHRGETBLE,2,FALSE)*C4

2. =IF(AND(I4>='Daily Rentals & Charges'!$A$36),I4='Daily Rentals & Charges'!$A$38),VLOOKUP('Daily Rentals & Charges'!$A$22,LATECHRGETBLE,2,FALSE)*C4)

Basically i am trying to calculate late fees for a rental company using the if and vlookup functions.

View 2 Replies View Related

Index Match Using Cell Combining

Jul 13, 2014

I am trying to use following string to find result into column F and unable to find reason for Error.

=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))

A
B
C
D
E
F

1
part
code
price
find part
find code
Result

2
x
11
5
y
12
#VALUE!

[Code] ........

View 1 Replies View Related

Determining Larger Value Cell & Combining With Another C

Aug 7, 2007

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?

View 9 Replies View Related

Combining A String To Reference A Cell

Oct 10, 2008

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.

View 9 Replies View Related

Combining Text From One Cell So That Appears On One Line

Mar 2, 2014

File I have attached or made a link for here Untitled-2.jpg

I am trying to make that amazon cell, all text to appear as one line like the second line foreign conversion fee i want it to appear like that. Rather then having to delete and modify 100's of rows like this.

View 6 Replies View Related

Combining Fixed Text In A Single Cell

Aug 20, 2009

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,

View 11 Replies View Related

Combining Text And Cell Reference With Indirect

Oct 2, 2008

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.

View 2 Replies View Related

Combining Cells Based On Value Of Adjacent Cell.

Jan 11, 2009

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

View 2 Replies View Related

Combining HTML Hyperlinks And Cell Contents

May 9, 2007

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]

View 9 Replies View Related

Combining Custom Formated Date With Text In New Cell

Jul 20, 2009

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.

View 2 Replies View Related

Combining Text In Several Cells Into One Cell F To Make Email Address

Mar 10, 2014

Cell A is the first name, B is a period, C is the last name, D is an "@" and E is "yahoo.com".

I want to squeeze them all together, in order, without spaces, into cell F to make an email address.

What is the formula ?

View 5 Replies View Related

Combining Contents Of Multiple Cells Into 1 Cell - Without Losing Data

Jun 5, 2013

Collapsing function: I want to select cells that I want merged, and have them COLLAPSE into the first cell selected calls
Combining function: OR Select the cells I want merged, and allow me to pick a destination cell for the result COMBINE

I would like to add this functionality to all my spreadsheets in the future.

Excel-Forum-Data Collapsing sample.xls

View 1 Replies View Related

Combining Names: If The Cell Is Blank The Result Should Not Leave A Space

Jan 16, 2009

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

View 5 Replies View Related

Leaving Blank Cells Blank In Dragging Formula Combining Different Formulas In One Cell?

Aug 2, 2014

I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.

View 8 Replies View Related

Combining/merging Multiple Rows From One Worksheet Into One Cell In Another Worksheet

Jan 28, 2010

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.

View 12 Replies View Related

Comma Separated Cell Values To Column Of Unique Cell Values

Mar 31, 2014

I have one column of data. there are currently ~10k rows, but this will increase over time. each row has either a single value (example: pepsi) or a comma separated value (google,samsung) with up to 6 commas. instead of the rows looking like this:

pepsi
google,samsung
coca-cola

I want them to look like this in one column (preferably via a formula):

pepsi
google
samsung
coca-cola

I would like the above output to be a unique, alphabetized list.

View 8 Replies View Related

Match Cell Value Then Copy And Paste Values Cell Values Into The Same Row?

Jun 27, 2014

write a script to match a cell value then copy and paste the matching row across two worksheets - "Master List" and "Demographics".

These two worksheets have the same cell values in Column A, the key difference is the Column A cell order in the second worksheet "Demographics". This Column A cell order is set arbitrarily by another system and will vary over time.

I would like to match the rows, then paste the three columns from the second worksheet into the first worksheet.

For example:

"Demographics" Worksheet Cell A2 = "Master List" Worksheet A45 - then copy cells A2, B2 and C2 and paste into A45, B45, C45 respectively.

"Demographics" Worksheet Cell A49 = "Master List" Worksheet A12 - then copy cells A49, B49 and C49 and paste into A12, B12, C12 respectively.

I have created a sample spreadsheet here; - List Schema.xlsx

View 2 Replies View Related

VBA To Fill Cell Values Based On Adjacent Cell Values

Jun 5, 2014

I request you to write a code for me to fill the cell values as "Not Applicable" in Column "AZ", if the "B" Column cell values = "Justified", "Approved LSAR" & "Approved SDAR".

I have attached the work book of what I am trying to accomplish.

View 14 Replies View Related

Adding Cell Values That Are Dependent On Adjacent Cell Values

Oct 27, 2009

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

Range is C40:D48

View 4 Replies View Related

ADVERTISEMENT