Concatenate 2 Columns To Make Every Possible Combination

Jul 19, 2009

Is there an easy way to concatenate two columns and provide every possible combination?

E.g.,
A B
1 A1 B1
2 A2 B2
3 A3
The function should result in
C
1 A1B1
2 A1B2
3 A2B1
4 A2B2
5 A3B1
6 A3B2

ADVERTISEMENT

Only Display Value If Combination In Two Columns Matches Combination In Two Other Columns

Jul 12, 2012

I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.

I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.

View 9 Replies View Related

Make Concatenate A Formula

Feb 24, 2010

I have a DDE code that returns a stock symbol value. The code is as follows, where @ES# is the symbol:

=NeoTicker|q!'@ES#,last'

I am trying to create a cell so when I put in a symbol (H10), it automatically gives me the quote. So I split up the code and then concatenated it as follows:

G10: '=NeoTicker|q!'
H10: '@ES#

View 9 Replies View Related

Getting Unique List From Combination Of 2 Columns?

Apr 18, 2014

how can I retrieve a unique list coming from a combination of 2 columns. Using VBA would be much better.

e.g.
Col1Col2
RED 1
RED 2
BLUE 1
BLUE 1
RED 1
BLUE 3
BLUE 2
BLUE 1
RED 1

Result will be

RED 1
RED 2
BLUE 1
BLUE 3
BLUE 2

View 3 Replies View Related

Finding Two Columns Giving Best Combination

Mar 23, 2014

I need to find the which two columns that give the greatest combination.

Row 2 shows some possible combinations, a total of combinations is 21.

Attached File : test.151515.xlsx‚Äé

View 12 Replies View Related

Identify Minimum Combination Of Columns Which Makes Row Unique?

Nov 20, 2013

I have a (phylogenetic) matrix with a species in each row, and morphological character in each column, scored as 0, 1, 2, 3 or 4 for each different species, so something like the following but much bigger:

Character 1 Character 2 Character 3
Species A 1 0 1
Species B 3 2 2
Species C 1 2 0

I want to identify the minimum combination of different columns (i.e. their values, but also which columns) that make a particular row unique.

View 14 Replies View Related

Excel 2010 :: How To Make Concatenate To Generate A Text String Using Custom Formatted Value Of A Cell

May 2, 2014

I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).

number_forma.png

I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.

In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";

custom_format.png

As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.

Here is a screenshot of what the original and custom formatted cells look like:

custom_formatted_cells.png

As you can see the custom formatting works and does exactly what I wan

The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).

To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:

CONCATENATE("SOME STRING ", B1)

Where B1 is the custom formatted cell.

The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:

concatenate_formula.png

As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".

I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)

My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?

*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.

View 8 Replies View Related

IF Formula - Column To Show Text Value Dependent On Combination Of Three Columns

May 12, 2014

I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.

It ends up being 18 variations so my IF formula just won't compute.

This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:

IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc

Any way I can get this to work for all 18 possible outcomes??

View 6 Replies View Related

Concatenate 2 Columns & Delete Orginal Columns

Sep 25, 2007

I had wanted to go through my spreadsheet and concatenate two columns (A & B)into one (A) then delete the duplicate column (B), but have found no way to do that. Now I am trying to search then insert a column prior to the other two, concatenate the data into the new column then delete the columns. I am specifically having a problem with my Range statement and can't figure out how to activate it or discern it after using the Find command.

Sub GroupGender()
Cells.Find(What:="Group", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireColumn.Insert Shift:=xlToRight
With Range("a1", Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 0) = "=RC[1] & "" "" & RC[2]"
.Offset(0, 2) = .Offset(4, 2).Value
End With
Cells.Select
Selection.Replace What:="Group Sex", Replacement:="Grp/Sx", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select.......................

View 4 Replies View Related

How To Concatenate Columns

May 16, 2012

I have the below set of data for example,

aaabbbcccdddeeeaaabbbaaabbbcccaaa

I need the output as,

aaabbbcccdddeeeaaabbbaaabbbcccaaa

As we know, a simple concatenate will work. But my columns are not limited and not easily countable. In my real scenario for some of the rows, data exist from column "A" till column "IB". So, I need to first identify the column data exist in each row and then to implement the concatenate function.

View 3 Replies View Related

Concatenate 3 Columns Into One

Jul 30, 2012

I'm trying to take the contents of 3 columns- A, B & C and have them form what I can use for a search string in column

D that I can use in a database I have.
Column A is first name
Column B is last name
Column C is Company Name

This is what i'm trying to get it to look like in column d:

("First Name Last Name" OR "FirstName.LastName") AND @companyname.com

View 6 Replies View Related

Concatenate Two Columns

Nov 21, 2006

writing a macro with following requirement

i need to display the third column with the concatinate values of column 1 and column 2

if A1 = Hi
B1 = All

then C1 should be Hi All

View 3 Replies View Related

Concatenate Columns

May 7, 2007

Item No amountinvoice no
2050377 14,870 105
5000085 120 1102
5000085 15,000 90197361
5000139 16,000 12346
5000237 31,000 56789
2050377 20,000 10111213
2050377 ($20,000)105

I have a spreadsheet with 3 columns. when I'm reimbursed money (see 2050377 for ($20,000) i'm trying to use VLOOKUP to search the spreadsheet and find out the invoice that relates to that reimbursement. but as you can see VLOOKUP finds the first incidence of 2050377 and gives me inv no 105 when it should be 10111213. my formula in C8 is =VLOOKUP (A8,$A$1:$C$8,3) but I need for the VLOOKUP to look at the item number as well as the amount. i've been told that I need to utilise concatenate, but I don't know how to do this.

View 4 Replies View Related

Concatenate Two Columns Of Text

Dec 25, 2008

In Excel 2007 I want to concatenate two columns of text. In Column A all the cells contain a single statement that I want to prefix the statements in the cells of column B (the statements in column B differ from cell to cell) I have used the formula =A1&" "&B1 and this is fine for that row, when I use the fill handle and pull it down the page the formula changes accordingly i.e.=A2&" "&B2, =A3&" "&B3 etc. But when I make the text appear using control+ I only get the concatenation of the first row repeated all the way down, irrespective of the contents of other cells in Column B.

View 14 Replies View Related

Continuously Concatenate 2 Columns

Jan 15, 2008

I have a series of data in 2 columns...say A and B...that I want to concatinate into a string of data...i.e A1&A2&B1&B2&C1&C2 etc etc....

Can I include this in a macro so that it will continue to concatinate the data until it gets to the end of the list (i.e a blank cell) ?

View 6 Replies View Related

Concatenate Words From 2 Columns

Feb 15, 2008

I need a simple macro to take a list of words in 1 column, and concatenate them with each word from another column

So if Column A had "bird"

and Column B had
food
feeder
house

the output would be

bird food
bird feeder
bird house

I will have many many more words than just "bird" though.

View 3 Replies View Related

Concatenate Multiple Columns With Spaces

Jun 21, 2008

I've searched quite a few times for Concatenating Multiple Columns and ones for Concatenating with Spaces.

I've found solutions that say use

=concatenate(A1, " ", B1)

or the VBA equivalent

I have at least 15-20 columns which I wish to concatenate.

Is there an easier way than to type for all 15 columns?

=concatenate(A1," ", B1," ", C1,....)

Also, the number of columns of each row vary

View 9 Replies View Related

Concatenate Function To Merge Certain Columns

Aug 14, 2009

I have a worksheet with 10000 plus entries in ten columns From K to T. I wish to have a macro with Concatenate function to merge certain Columns from these ten columns, in one column with help of input boxes which may ask inputs, about range (from and to ), and number of digits to concatenate in the required column. I use Excel 2003 XP in work but an example is 2007 attached.

View 5 Replies View Related

Concatenate With Loop Through 2 Columns Of Information

May 28, 2014

I have a list of names with blank cells in Column A cell 2.

I want the programme to scroll through this column until it finds a name, when it does I want it to look in cell offset(1, 1), in this case B3 to see if there is a number. I want this number to be moved to offset (0, 1) AND any subsequent numbers below it until the next blank cell in that column (B).

In the attached image cell B2 will contain all numbers in cells B3:B9 with a / between them. Cell B10 will have number from B11 and B12 will have B13:B15. If there is no number it will just continue to the next name in column A. My rough code below delivered the first number only in the relevant cell and I'm not clever enough to get it right. It does not have to be super slick as it's for one time use.

I need numbers in column B cleared as they are concatenated into the single cell.

Spreadsheet.jpg

View 12 Replies View Related

Auto-Concatenate Columns-row Within Worksheets

Mar 21, 2007

How do I create a code that will concatenate the number in Column B, C and D into Column E for each row within each worksheet of the file, excluding the "Update" worksheet. If there is no number is each column of the row, then do not produce a record for that row in column E. Start executing function on row 2.

eg E2=b2&c2&d2=047, E3=b3&c3&d3=964, E4 cell is empty, because there is no record in b4, c4 and d4

View 9 Replies View Related

Concatenate Columns Into File Path

Nov 15, 2006

I am creating an excel file for a mail merge following this process. I need to take the three columns below and enter the path to the corresponding chart. I have all the charts created in a directory. The naming convention for all of the files is XX-XX-XX.gif. The X's represent the numbers below without the decimal places. Therefore the example below would be 44-43-27.gif. Is there anyway to create a formula to drag down instead of entering the filename's manually? In the end, I want to have the full file path as shown in the XLS.

q_3_abc q_3_teamq_3_yscore
4.4 4.3 2.7

I have 700+ rows to do this for, therefore I want a formula or VBA solution instead of the manual donkey work.

View 3 Replies View Related

Concatenate (Join) Cells Across Columns

Feb 9, 2008

I have a marco that works perfects to Concatenate (join) all of the cells in Column A. However, I want to seperately join all of the cells in each column. So I wanted to the Concatenate of Column B to go in B1, Column C to go in C1, etc (or until there is no data in a column). I think there is a way to change this into a loop, but I don't know how. Below is the macro I have now.

Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("A2:A300")
For Each cel In rng
x = x & cel.Value
Next
.Range("A1").Value = x
End With

End Sub

View 2 Replies View Related

Vlookup And Concatenate Multiple Columns Of Date?

May 8, 2014

Say in L4 on form test i want to look for the value of A4 in Drill data range a2 to z1000 and retun a concatenated string of something like "sav 407 : 08-may to 11-may"

View 3 Replies View Related

Nested Loop To Concatenate 2 Columns Of Data

Oct 27, 2009

I think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.

View 3 Replies View Related

Excel 2007 :: Consolidate Several Columns Into One (not Concatenate)

May 6, 2014

I have the following situation and I'm trying to do this using a PC with Excel 2007.

I would like this:
T
N
O
O
C
O
E
I
M
I
T

[Code] ........

To look like this:

T

H

A

N

K

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

This procedure will become part of a macro. The number of rows will vary but the number of columns will be the same (11 columns). I don't care too much about the order since I can later sort. As long as the data from all columns in now in one column.

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

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

The result would be:

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

View 6 Replies View Related

Concatenate Multiple Columns But Ignoring Blank Values

Aug 13, 2012

I wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?

View 4 Replies View Related

Concatenate Cells In Columns & Change Part Of Font

Apr 12, 2008

I need for it do perform the same action on multiple rows/ cells of data. What adjustment to the code needs to be performed to do so?

Sub fconcat()
Range("C1").Value = Range("A1").Value & Range("B1").Value
For i = 1 To Range("A1").Characters.Count
Range("C1").Characters(i, 1).Font.Name = Range("A1").Characters(i, 1).Font.Name
Next
For i = 1 To Range("B1").Characters.Count
Range("C1").Characters(Range("A1").Characters.Count + i, 1).Font.Name = Range("B1").Characters(i, 1).Font.Name
Next
End Sub

View 3 Replies View Related

Macro To Concatenate Variable Amount Of Columns Based On Cell Value

Jul 23, 2014

I have a table that looks like:

7300
3

7301
2

7302
5

And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.

View 7 Replies View Related

SUMIF - Trim And Concatenate 2 Sets Of 2 Columns Giving Answer In Another Column

Jun 22, 2008

I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.

Iíve seen sum product and havenít been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failingÖ

A = 2000 text
B = 1234 text

F = 2000 trailing spaces, text
G = 1234 trailing spaces, text

I = 10,000

Answer in column J

I want 20001234 = 20001234 = 10,000 for the whole column.

I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.

View 9 Replies View Related

How To Make Columns Read Only

Aug 7, 2014

Is it possible to make Columns Read only? So not one can type or edit. Not all columns just the ones I need

B,C,D,I,J

View 2 Replies View Related

Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List

Oct 6, 2007

I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.

ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C

I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.

ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C

View 5 Replies View Related

Make Columns Start From Left To Right (A ,B, C, ...

Dec 22, 2009

I recently installed 'windows 7 ultimates' , and after i insalled Excel 2007 .. the columns start from right to lfet (..., C, B, A), I want to reverse it to start from left to right (A, B, C, ...), I don't know whether the new ystem 'windows 7 ultimates' has some effect on it to be modified like this. moreover, the language of system is English. so how to solve it

View 3 Replies View Related

How To Combine Two Columns To Make Phone #s.

Feb 1, 2010

I have two columns of numbers. The first is the area code and second is the phone number. How can I combine the two columns to make one with both: as in area code-phone number?

View 3 Replies View Related

How To Make Columns In Listbox In Userform

Mar 20, 2013

I made an excel MAcro Sheet as I am not good in macros. How to make a columns in list box via text boxes in userforms.

View 2 Replies View Related

Freeze Columns And Make Them Non-scrolling?

Jul 21, 2014

I have a worksheet in which I want to freeze columns A and B, and either make them non scrolling or 'fix' rows 1 - 25 so that they are permanently in view.

View 4 Replies View Related

Make A List Of Unique Values Using 3 Different Columns?

Feb 26, 2014

See attached file with a smaller sample of the 1667 row table of data I am working with. What I am looking to do is make a list on another sheet of each every line with a county and have the corresponding Township and range with it. So if I have a cell and I select Audubon county, I would like a list to populate that has the 12 lines of Audubon county in column A, and the Township in column B and the Range in column C.

Excel Rate Example.xlsx

View 5 Replies View Related

Make Columns Visible/invisible As Per The Conditions

Sep 4, 2009

I am trying to create a daily task activity sheet. I would like to know, how to make the columns visible and invisible as per the selection from previous column.

For eg:

Column A has a list which has values 'TC creation' , 'TC modification', 'System Test' etc.

Upon selecting TC creation, i need to make a column visible ie 'total tcs created'.

Upon selecting 'TC modification' i would have to make the previous column invisible and make a new column visible for entering no of tcs modified.

Upon selecting 'System test' the other columns should go invisible and make other 3 columns visible for no of tcs executed, no of tcs passed, no of tcs failed etc...

View 12 Replies View Related

Make A Mcaro Loop Through 5 Pairs Of Columns

Jan 26, 2009

I've managed to get my macro to work ok - but what I want to do now is to get it to loop through more columns. Currently it looks for email addresses in column AH (which will always be the same) then it looks for a "yes" in columnAB and if there is no "SENT" in column T then it sends an email,after which it adds the word "SENT" to column T. What I want t do is for the macro to then run on columns AC+U, AD+V, AE+W and AF+X. So basically it will loop through one column to the right each time for a total of 5 pairs of columns

View 2 Replies View Related

How To Make Excel Report Width Of Columns(s)?

Jan 13, 2010

I'm looking for a macro or function/formula to list & total a range of column widths. Having lots of columns with varying widths t'would rather not have to do it manually from each column's Properties.

View 9 Replies View Related

Concatenate Non Blank Cells But Use Concatenate And Substitute Instead Of IF

Aug 11, 2013

Sampling table :

one
two
three
four
one
two
three
one
two
one

Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")

one , two , three , four
one , two , three
one , two
one

Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?

My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,

View 9 Replies View Related

ADVERTISEMENT