Code To Join Dots

Dec 16, 2009

Cells E39:E53 are conditionally formatted to fill red if cell $AB$13=the cell 3 to its left .. eg: cell E39 would fill red if cell B39 (the cell 3 to the left of E39) is equal to cell $AB$13

Only one cell in the range will meet the condition at any time.

I also have the same range conditionally formatted to fill black if cell $AF$13=the cell 3 to its left .. eg: cell E39 would fill black if cell B39 (the cell 3 to the left of E39) is equal to cell $AF$13

Ranges F39:F53, and G39:G53, H39:H53 etc etc etc (all the way up to X39:X53) are all conditionally formatted the same way.

What I would like is for the cell that fills red in the first range to have a line connecting it to the cell that fills red in the second range, etc etc etc.

And the same for the cells that fill black.

Creating a chart from my data is not an option for other reasons, so I'd like to explore this possibility.

View 9 Replies


ADVERTISEMENT

Concatenate-Join Quote Marks Around Text Code

Oct 10, 2006

Trying to build array elements from a list

19909
19953
etc

My thought is in helper column to concatenate the quotes and comma "xxxxx", Then I will copy paste special transpose and should have an array list

I tried ="""&A2&"","
Returns
"&A2&",
But I need it to return "19909",

View 4 Replies View Related

Connecting The Dots

May 2, 2007

I have 1 row of user entry cells (A1:Z1). To keep it simple, let’s say A1 is always 0 and Z1 is always 100.

The user can enter any positive number in any of the cells. For Case 1, let’s say ‘20’ in M1.
I want a set of formulas in A2:Z2 that fills in the values with straightlined values from 0 to 20 from A2:L2 and 20 to 100 in N2:Z2.

For Case 2, the user enters ‘10’ in M1 and ‘60’ in T1 and the formulas in A2:Z2 fill in the appropriate values that connect the empty cells (i.e., 0 to 10, then 10 to 60, then 60 to 100).

Basically, a user enters 1 or more numbers in 1 row, and the second row fills in all the non-entered cells with #s that are straightlined, thus ‘connecting the dots’.

I think a bunch of nested IF statements might work, but it’ll be hairy and nested IF statements are a resource hog. Any better ideas? I’m think maybe some INDEX and MATCH functions.

View 9 Replies View Related

Plot Dots On A Map With Data

Mar 30, 2014

I am planning on creating a map of a badminton court. I am hoping to record each hit of the shuttlecock with a dot on the map. Of course I want this to be inputted by data rather than manually inserting a circle shape and placing it in the correct location each time.

is there any way to do this in excel?

View 14 Replies View Related

Counting Black Dots

Dec 18, 2008

I use a black dot (Unicode hex 25cf ) and I would like
to count those dots.

I am using the formula below but it doesn't seem to work.

=COUNTIF(A9:A14,CHAR(25CF))

View 11 Replies View Related

Replace Dots For Comma Both Columns?

Apr 7, 2014

I got VBA line that will replace Dots(.) for Comma's (,)

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

The problem is its only take collum I and i want it so that it will take I and J.

am I doing something wrong here that it only take I and not Both ? I and J?

View 4 Replies View Related

VLOOKUP To Miss Blanks (the Dots Are Blank Cells)

Apr 15, 2008

I have about 90+ lists of data which looks like this: (the dots are blank cells)

Set 1
D0 Jimmy
...
D1 Keith
D2 Dave
...
...
D3 Sandra

Set 2
...
D0 Andy
D1 Ryan
...
...
...
...
...
D2 Lee

Each list was originally a set of names and i have populated the "D1, D2" cells (where D1 = the first name, D2 = the second, etc).

Is there any way, on a seperate sheet i can get a list which shows the following (ie. no spaces between lines of data):

Set 1 Set 2
Jimmy Andy
Keith Ryan
Dave Lee
Sandra

Is this possible through a VLOOKUP?

View 11 Replies View Related

Replace Dots In Dates With Valid Date Separator

Feb 19, 2009

I have a SAP application that outputs a field of data from a specific query. One column, the date, is produced in the format 18.02.2009. Is there any excel 'trickery' that can convert this into a standard british date format i.e. 18/02/2009 & then allow me to sort the entire data field by ascending date order?

View 6 Replies View Related

Extract Middle Words From Text String With Multiple Dots?

Oct 22, 2012

black.blue.red.yellow.green.white

I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.

I've tried using RIGHT, LEFT, MID formulas unsuccessfully

View 3 Replies View Related

Sql To Join Two Table

Aug 10, 2008

how to join two tables which their relationship is one column in A table inclue another column in B table.

for example:
Table A:
ColumnA1
ColumnA2

Table B:
ColumnB1
ColumnB2

I want to join Table A and Table B, and the where clause should be A.ColumnA1 include B.ColumnB1.

Like the value of A.ColumnA1 is "Abc1234 test", and the value of B.ColumnB1 is "1234", and then we can join the TableA and Table B

View 14 Replies View Related

Join 2 Cells In VBA

Dec 6, 2007

I'm writing an Excel Macro and want to sum a number cell with the value "45667" and a text cell with the value "Prague" and return it to a new cell with the value " 45667 Prague"

I've done it successfully with the two text cells "John" and "Hopkins" to "John Hopkins".

This is my

Sub SumCells()

Dim FirstName, SecondName, Zipcode, City, fullname, fulladdres, space

FirstName = range("cell1").value
SecondName = range("cell2").value
Zipcode = range("cell3").value
City = range("cell4").value
space = " "

fullname = firstname + space + secondname
fulladdress = zipcode + space + city

The problem is that city is recognized as "Prague", and the zipcode as 45667 (without the "")

View 3 Replies View Related

Join Rows Of Data?

May 26, 2014

We have approximately 100 rows x 200 columns of data and would like to combine the contents into one row. Is this possible without copying and pasting many times.

eg

1 abc dek jui kol
2 ppo adf asd dfa

into one row

1 abc dek jui kol ppo adf asd dfa

View 3 Replies View Related

Join Two Record Sets From Different DB Using Vba?

Jun 9, 2014

I have an requirement that retrieving the data from two different data bases. I want to compare those two and prepare a report using VBA. I am able to get the data to excel separately, but now I need to join these two sets based on the key columns

View 2 Replies View Related

How To Join Cells In Different Columns

May 31, 2012

I have a list containing hundreds of rows and I need to join the cells in different columns.

Eg. Col A, Row 1 is "Red Maple"
Col B, Row 1 is "Acer Rubrum"
Col C, Row 1 is "60mm"

I need to join the row into one cell reading "Red Maple Acer Rubrum 60mm"

View 2 Replies View Related

Join The Text Formula

Feb 26, 2007

Column A2:A100 contains part description various len lengths max is 50 min is 22.

Column B2:B100 contains scrap qtys.

In column C2:C100 in need to join the text in A and B.

i want the scrap qty's all to be alligned i.e level, as column C is shown in a userformlist box.

meaning joining text/values would look like this (ignore full stops)
dispenser calsberg .........70
tap beer......................... 80
cowl................................100

instead of
dispenser calsberg 70
tap beer 80
cowl 100

View 9 Replies View Related

Join Two Cells Of Data

Apr 20, 2009

I want to take a cell with a persons first name and a cell with their last name and combine into one.

EX.
What I have.
A1 B1
John Smith
Walt Smith

What I want.

A1
John Smith
Walt Smith

View 9 Replies View Related

Join Arrays In Formula

Jun 14, 2009

I have a long OR function that I'm working on. The logicals for this change the search value from 1 to 9. Here is one of the logicals:

sumproduct(--isnumber(search(1,???)))=0

I need the "???" to be the arrays. The most complex of these will be: A1:C1&a2&c2&a3:c3 or is it
A1:c1,a2,c2,a3:c3 or perhaps another join method like
concatinate(a1:a3,a2,c2,a3:c3)

How do you join arrays to get the right result?

View 9 Replies View Related

Search For Name & Copy/Join To Corresponding Value

Aug 22, 2009

I'm dealing with 4 columns. Column A is a list of names. Column C is also a list of names, each with a number beside it in Column D (i.e "John Smith" in cell C1 with "10" beside it in cell D1). I need a formula that can search the list of names in Column A for the matching "John Smith", then put the corresponding number ("10") beside his name in Column B. Meaning...if the search finds the matching "John Smith" in cell A6, I need the formula to copy "10" to cell B6.

View 2 Replies View Related

Join Text With Cell Value

Dec 5, 2006

I am trying to change the value in a cell by referenceing it to another sheet. My cell is: Today it is 33F, have a nice day. Now, 33F should be called from another cell and should be inserted in this new cell. If I change the 33F in the other cell this change should be reflected in my text file.

View 3 Replies View Related

Join Text & Chr Function

Dec 13, 2006

I have a multipage control on a userform. On Page 8 of that multipage, I have label1, part of that label is modified by other parts of the program. This is done by the program writing to a sheet of Data and then the program pulls that info into this label. My question is,what is the correct format to populate the label?
see below: This is what I have in my Userform_Activate and Userform_Initialize subs:

CurrentVersion = Sheets("Data"). Range("N2").Value
Label1.Caption = "This will allow you to change the Caption of the UserForms through out the operational program. Some UserForms cannot be changed. The ending of" + Chr(32) + Chr(34) + "Automatic 2005 V" + Chr(32) + CurrentVersion + Chr(34) + Chr(32) + " will be added to what you enter below as default."

CurrentVersion is pulled off of the datasheet which is a number like 5.9.8.2. however it errors "Run time error 13 - type mismatch" I can only suspect the label1.Caption needs something like: Multipage8.label1.caption =

View 2 Replies View Related

Join Text To Cells

Jan 10, 2007

I have a column with numbers in each cell. I need to add the same three letters before each of the numbers. Example:

number in cell: 25067 I need it: ABC25067

I did a macro for it but had to do each one individually. I imagine there is a simple formula to do this automatically but I don't know enough to figure it out (despite reading up on it for the last hour!)

View 9 Replies View Related

Join Cells With Space

Aug 7, 2007

I have a sentence in field A1 and a number in field A2. The formula I am using is =A1 & A2. This is working but there is no space in between A1 and A2. How can I add a space?

View 2 Replies View Related

Join 1st & Last Names Randomly

Apr 22, 2008

This request is for Excel 2003 rather than 2007 (company does not have Vista or 2007 as I do personally). When two columns are used for an employee's 1st Name and 2nd Name, what is the random formula for combining BOTH columns in a random name pull? Since two people may have the same LAST name, only by accessing both column one (typically LAST name) and column 2 (first name) can they be identified if both last names appear in a random pull. I have used the random generator available from Excel Help online but do not know how to modify the parameters to accomplish keeping the first and second names from Column A and Column B together in the pull.

Explaining this will be widely useful for any company (such as trucking) using Excel for employee first and second names in two columns (assume A1 header 'Last Name', A2 header 'First Name'). Such companies need to be able to do a random pull to comply with DOT (Dept of Transportation) CDL re-certification, substance abuse screens, etc. I am just above a NOVICE user (I have a dozen books I try to learn from before asking).

View 4 Replies View Related

Join 1 Column With Last Character Of Another

May 3, 2008

I need to take the number from column 1 and add the last letter from a string in column 2 for input into a third column:

ex:

col1
9780870704079

col2
AMOPBKBB

col3
9780870704079B

View 3 Replies View Related

Macro To Join Two Columns With Delimiters?

Jan 14, 2014

I'm trying to write code to join two columns of data, and then join all of those into one cell.CA

California

DE
Delaware

CT
Connecticut

I want to be able to run the macro and have it put {"CA":"California","DE":"Delaware","CT":"Connecticut"} into cell C1.

I was thinking that this would have to be a two part macro. First, it would take each specific row and join CA and California and add the colon, quotation marks, and the comma, and put it Column C. Then, after it's gone through every row, it would join every cell in column C together.

This is what I've written so far:

VB:
Sub test1()
Dim r As Range
Dim lr As Integer

[Code]....

Now I need to figure out the second part. How do I then take all of the join cells in column C and join them together, adding the curly braces at the beginning and the end, and removing the comma from the very last row?

View 4 Replies View Related

Concactenate To Join Two Vlookup Formulas

Dec 16, 2009

Can you use two vlookup formulas as the text field in a concactenate formula? See below for example the formula I am using that is returning an #NA formula.

=CONCATENATE(VLOOKUP(A2,qacontact,6,FALSE),VLOOKUP(A2,qacontact,7,FALSE))

View 2 Replies View Related

Join Array With Text Elements?

Jun 30, 2014

Join an array with Text elements to create a string that can be Evaluated

So for instance if I have Array("A", "B", "C") and I want to evaluate("=({" & Join(array, ",") & "})="A)"). Is there any way to do this without having to loop or push to a Named array first? I'll even take this evaluate thing if I can do it with text and numbers

View 6 Replies View Related

Join Various Columns From Two Data Sheets

Jan 6, 2009

I wish to join few columns from two data sheets using Macro function.

Sample
Sheet 1:
A B C D E
1 Headline Msg1 Msg2 Msg3 Msg4
2 Car Post Join Help AAA

Sheet 2:
A B C D E
1 SumNo Msg1 Msg2 Msg3 Msg4
2 CM001 Join Help AAA BBB

Output;
A B C D E F
1 Headline SumNo Msg1 Msg2 Msg3 Msg4
2 Car Post Join Help AAA
3 CM001 Join Help AAA BBB

View 4 Replies View Related

Look Up Same Entry And Join Cell Content

Dec 14, 2009

i have a large spreadsheet of more than 400.000 address entries. The data is sorted by tables "A=name" "B=city" "C=country"

I need to join cells of Table A whenever B(city) & C(country) match.

Is there a formula which:

Looks up tables for cells with same "country" + same "city" = join all the names of Cell A (names)

Example: ....

View 7 Replies View Related

Join 6 Workbooks Together To Make One 7th Workbook

Jun 27, 2013

How to do this with a macro but I want to join 6 workbooks together too make 1. Master sheet (7th copy). Below are the points of interest and a couple of questions.

1- My six books are all identical in cols.
2- The six books all have in col A my own ID number, no ID number is the same in any worksheet.
3- If I can get all 6 books together then the ID will start at #1 and finish approx at 225,000, in accending order.
4- All books have only 2 worksheets inside, Sheet1 and Sheet2 .
5- I don't want to delete or loose any data in the 6 books already made. Just make a 7th copy of the 6 .
6- The 6 w/books are called
6A- Each w/book contains approx 40,000 rows except last , approx 22,000

UPTODATE STAKESWTD SECT 1
UPTODATE STAKESWTD SECT 2
UPTODATE STAKESWTD SECT 3
UPTODATE STAKESWTD SECT 4
UPTODATE STAKESWTD SECT 5
UPTODATE STAKESWTD SECT 6

7- There are no functions in any of the w/books, just other macros which I want in the master copy.
8- Can the 7th copy be called UPTODATE STAKESWTD MASTER

View 2 Replies View Related







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