Sum Of Single Cell Arrays
Feb 14, 2013I have {1,2;3,4} in *one* cell, and {9,7;8,10} in another cell. How can I sum those arrays? The result may be in one cell, or in a 2x2 range. Plain Excel, no VBA.
View 6 Replies
ADVERTISEMENT
Store / Retrieve Arrays Within Single Cell?
Aug 31, 2012I have created a number of complex functions that use or create 1-dimensional numeric arrays in VBA, and I would like to store and retrieve some of these arrays within single cells of a worksheet so that I can use them in dependent functions without having to store and display the entire array with one element per cell, and without having to recalculate the same intermediate array multiple times within VBA.
Are there any existing worksheet or VBA functions (or is it possible to create two functions) that can convert and store an entire numeric array within a single cell of a worksheet (e.g., as text), and then convert this back into a form which can be read and recognized as a numeric array by another function?
Or is there any other way to avoid filling my worksheet with arrays, or having to recalculate them each time within VBA?
Slicing And Dicing CSV Files - Involves Arrays And Jagged Arrays
May 8, 2013I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
Only Allow Cell To Contain Any Combination Of 2 Arrays?
Jan 23, 2014I was just wondering if it was possible to only allow cells in a worksheet to only allow values that are a combination of 2 arrays whether it be through data validation or other means. For example, if I have an array that has a b and c and a second one with 1 2 and 3, is it possible to only allow values a1 a2 a3 b1 b2 b3 c1 c2 c3?
View 2 Replies View RelatedLookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)
Jan 7, 2009If 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
Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format
Mar 21, 2008I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...
View 9 Replies View RelatedExcel 2010 :: Return Range Of Cell Concatenated Based On Single Cell Value
Jul 2, 2012I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001
q9002
q9003
q9004
q9005
q9006
etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
(Using Excel 2010)
Find Multiple Instances Of Single Criterion In Row & Return To A Single Col
May 8, 2006Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.
I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.
I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.
NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.
Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0
Scenario:
Looking for Numeric Criterion 1 (one).
Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1
In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.
Paste Single Cell From External Source To Merged Cell In Excel?
Dec 2, 2013I Want users to be able to copy a reference number from a an external program into excel, this works well for single cells but the cell I want them to be able to paste the information into is a merged cell and keep getting the "Data on the clipboard is not the same size and shape blah blah error". What I can do to get this information to paste? Is there some VBA that can kinda trick the cell to believe its single or when pasting it unmerges and then remerges again?
View 1 Replies View RelatedVBA Loop - Remove Single Character From Cell String Then Calculate New Value In Cell
Mar 1, 2012I have a string of text in cell A2. In cell B2 of my spreadsheet is a formula that calculates a number based on the text string in cell A2.
I want to write a VBA loop that removes a single character from the cell A2 string, then calculate the new value in cell B2. I want this loop to continue until the value in B2 falls below a set value (in this case 60).
My code so far
Sub trim_text()
Dim mytext As String
Dim myanswer As Integer
mytext = Range("A2")
myanswer = Range("B2")
Do While myanswer > 60
mytext = (Right(mytext, Len(mytext) - 1))
Loop
End Sub
This obviously does not work. In my excel table I have a formula in cell B2 to calculate "myanswer" will this work, or does that code have to be placed into the VBA code?
Finding Single Name In Cell In Cell With Multiple Names (words)
May 8, 2014Looking for a formula (not macro) that can do the following:
I have a spreadsheet and in one column, it has last names of clients. In the next column, it has full names of clients that are the same account, but different system with different descriptions for the name. I need to lookup the column that just has last name and see if that last name exist anywhere in the "Full name" column. If it does, then I need column C to say "Yes" or something along those lines.
The "full name" column isn't always in the same format and sometimes doesn't even contain the "last name." Sometimes the last name will be first and sometimes there's a comma and sometimes there isn't a comma. The spreadsheet attached shows a good short example of what I'm working with...
Name Spreadsheet Ex.xlsx
Pull Keywords From Cell Range And Combine In Single Cell
Aug 1, 2014I am in need of a way to pull keywords listed in B2:B10 from text in A2:A10 and then those pulled keywords combined in cell C11.See attached document. So I have colors for keywords in column B and some random text in the column A including the keywords in parenthesis. Is there a way for a formula to find all keywords in A2:A10 and then list them in cell C11 with commas in between.So the result would be one cell C11 showing BLUE,GREEN,BLACK,ORANGE, etc.I have been trying to mess around with index and match and while I could get one to pull up I don't know how to combine it.
Keyword Range.xlsx‎
Format Multiple Cell Values Displayed In Single Cell?
May 24, 2012I have the following data on a spreadsheet created by a formula:
A1: 1.1234
A2. 2.234
A3: 3.34
I have the data formatted to only have two decimal places so it looks like this:
A1: 1.12
A2: 2.23
A3: 3.34
I'm using the following formula in cell B1 to display the values of those cells in a single cell in order A1,A2,A3:
=A1 & "," & A2 & "," & A3
However when the values of A1, A2 and A3 appear in the in cell B1 they appear as: 1.1234,2.234,3.34
Is there a way I can edit the formula or format something so the values in B1 appear as with only two decimal places? i.e 1.12,2.23,3.34
Formatting Cell Currencies Based On A Selection In A Single Cell
Jul 28, 2009I would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.
View 9 Replies View RelatedCheck Cell Rows Based On Numbers In Single Cell
Jun 12, 2007I have problem with function, I have array with random numbers in cell G1 and columns with X in some of the cells, now I want to check the cells with numbers from cell G1 in column F to see is there X or not if yes count if not do nothing. In attached example result should be 3.
View 7 Replies View RelatedExpand/Create Single Cell Alpha Series From Cell
May 15, 2008I have a cell with data such as: a0001-0004, a12, C8AF7-8, b17, j35-40 and i want it to output in the same cell(basically write-over the data): a0001, a0002, a0003, a0004, a12, C8AF7,C8AF8, b17, j35, j36, j37, j38, j39, j40.
View 4 Replies View RelatedCOPYING A SECTION OF DATA FROM A SINGLE CELL TO A NEW CELL
Dec 23, 2006123569LOC23 is in one cell and I am trying to copy the "LOC23" to a new cell. However, the amount of digits vary: LOC23, LOC1,LOC15 etc all the way down the column.
Put Both Text And Cell Value From Another Sheet In A Single Cell..
Feb 15, 2010I'm trying to put both text and cell value from another sheet in a single cell.
I've tried:
='sheet'!cell"text"
Doesn't work.
Also, I understand this is somehow it's supposed to be done
="Total: "&A12
But I need the cell data to be from another sheet and also I need the text to succeed the cell value.
Going by the above example, I tried:
='sheet'&cell"text"
But that also doesn't work.
Pasting From Single Cell Into Merged Cell
Sep 15, 2012I have a large data which is placed in single cell and I want to copy it into a template which is merged cells any I'm stucked. My data is very large and I can't unmerged the destination data(they are merged vertically). So I wonder is there a way to copy a single cell in a merge cell.
View 1 Replies View RelatedSearch For Each ID In Single Cell & Return Corresponding Cell
Jul 3, 2009to search for specific text in a cell but not just one word or letter, in fact it could be many. I need to first verify that the exact texts exist in a table and then match the various text instances with corresponding data in that table.
At the moment, this works to search for one type of text and verify it's exsistence in the able and indeed return the corresponding value, but i need it to do it for all verified texts
=(INDEX(A2:A4,MATCH( LOOKUP(9.99999999999999E+307,SEARCH(" "&$B$2:$B$6&" "," "&D2&" "),$B$2:$B$6),B2:B4,0),1))
Example attached
Splitting Single Cell Into Two
Aug 11, 2014Split the single into two, is this really possible in excel?To be precise dividing cell A1 into two different cells(No merging, No text to column option).
View 4 Replies View RelatedGet A Single Cell To Look At An Array
Mar 10, 2009What I’m trying to do is get a single cell to look at an array, if there is a number in that array which is between 2 limits to return that number. This is eventually going to work with limits of dates/times, and have to be updated once a day. I’ve attached a workbook with an example of the data and the way I want it formatted. {=IF(AND(A1:A25>=D32,A1:A25<E32),A1:A25,"")}
But this always returns a false. I can get it to work without using array and just having cell to cell logic but this means I would need the same amount of columns in the formatted data as the raw which is impractical.
Incrementing A Value In A Single Cell
Sep 28, 2009Is there a way to count how many times a value is changed?
Cell A1 can be either “YES” or “NO”. Its' value is controlled by validation…
When Cell A1 is changed from “YES” to “NO” I want to increment the value in B1 by one.
When Cell A1 is changed from “NO” to “YES” I want to leave B1 as it is.
Pasting Into A Single Cell
Apr 10, 2013I'm working with the sort of data that spills over into multiple cells if you try to paste it without first putting the cursor in the function window. Is there any way to change this default behaviour (or, alternately, automatically redirect the cursor to the function window every time I hit "enter" and go to the next cell)? I'm working with literally thousands of entries, so the time spent double-clicking and/or manually redirecting the cursor to the function window adds up after a while.
View 1 Replies View RelatedUnprotect Single Cell Only
Jul 10, 2012I have a spread sheet that is protected. I would like to have cell G5 unprotected when a checkbox is checked. I am able to unprotect the entire sheet by checking the box but I only want G5 unprotected. My current macro that unprotects the entire sheet is
Sub Checkbox_Clear2()
Sheets("Input_Personnel").Unprotect ("1")
If Range("J12") = False Then Range("G5").ClearContents
End Sub
Is there a way to change the highlighted line where it only unprotects cell "G5".
Merging Value In Different Row Into Single Cell?
May 1, 2013I have a data set
In column A , the data is present , and in Column B is the result which I require
How to achieve this .
The Data in Column A is basically split into different row , whereas i Want the data to appear in single line as soon as the next data is started . Example
Column A > SSC till 45.6 Is a single data which is appearing in different row . However there is another data which is also correct in column A I.E SSC CVBF 46 . Which means ignor the correct data present in Column A , and correct the splitted data in column A . Sample file attached .
Data
Result
SSC
[Code]....
Use Variable To Name Single Cell VBA
Jan 18, 2014I am using this string of code below to name a cell by converting variable Cmt1(Long) into a string variable cellname. I cannot get the line of code below to run. It works fine if I assign a string in "" marks to the variable. I need the variable cellname to match the contents of Variable Cmt1, which in theory it does as written. This is all part of a much longer string of code which runs just fine. I am just an entry level VBA user. I understand objects, properties, variables, etc.
Dim Cmt1 As Long
Dim celln As Range
Dim cellname As String
cellname = CStr(Cmt1)
'cellname = "FixedStringName"
How Many CHARACTERS Can You See In SINGLE CELL
Nov 29, 2006Let me state first that I do not see the need to put so much in one cell. Aren't there enough cells ?
Secondly, note the difference between DISPLAY and actual CONTENTS (see formulabar) of the cell. Contentslimits: see helpfiles, but displaylimits check out this code. It will explain itself. Experiment with settings within code.
Option Explicit
Sub count_characters_in_cell()
'Erik Van Geit
'061019
Dim cell As Range
Dim nr As Variant
Dim x As Integer
Dim temp As String
Set cell = Sheets(1).Range("A1")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sum Totaling Of A Single Cell?
Dec 15, 2005Is there any way that one would be able to sum total a single cell?
I have my numbers listed in column A & would like the return in column B .....
Autosum For Single Cell In Vba
Feb 22, 2007I have a macro that takes a large block of info, sorts it by certain criteria, then adds blank rows, and puts in Autosum for the various blocks of info. Problem occurs when a particular "block" of info is only 1 row. Any dirty little tricks to keep the macro running past this roadblock? The section of code looks like
Range("O1").Select
ActiveCell.Offset(2, 0).Select
Do While ActiveCell <> "end"
If ActiveCell.Offset(-1, 0) = "TOTAL" Then
ActiveCell.Offset(-1, 1).Select
ActiveCell.Font.Bold = True
SendKeys ("%="), False
SendKeys ("~"), True
ActiveCell.Offset(1, -1).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
