How To Sort Two Arrays

Jul 18, 2014

I have two arrays that output as two columns next to each other. Like this:

ID
Responses

1
4
2
3
3
2
4
5
5
1

I would like to sort the responses column and have it show as this:

ID
Responses

4
5
1
4
2
3
3
2
5
1

I am guessing that I need to some how merge them into one array so I can sort them as one entity rather than sorting two columns separately.

Here is my sample data and the macro I use to pull the data i need for the two arrays. sample.xlsm

View 2 Replies


ADVERTISEMENT

Slicing And Dicing CSV Files - Involves Arrays And Jagged Arrays

May 8, 2013

I 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

View 1 Replies View Related

Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending

Oct 5, 2007

I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending

ex) 1.751
2.52
3.753
418
417
416

View 5 Replies View Related

Move And Sort With One Column But Insert Extra Columns As Needed For Proper Sort?

Jan 13, 2014

Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.

Edit: Not sure what happened but file was NOT understandable before. It should be correct now.

View 2 Replies View Related

Sort Command - Custom Order Or Sort Ascending Or Descending

Oct 8, 2006

Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.

Cell A1 = A
Cell A2 = B
Cell A3 = C
Cell A4 = D
Cell A5 = E

If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.

Cell A1 = C
Cell A2 = D
Cell A3 = E
Cell A4 = A
Cell A5 = B

View 4 Replies View Related

Sort Worksheets Using Custom Sort To Choose Certain Word?

May 30, 2012

As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.

is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.

View 1 Replies View Related

Using Arrays With IF & AND

Feb 3, 2010

I have been trying to solve this problem by breaking it down to it's core elements and it seems to hang up at a certain spot but I can't figure out why.

formula is: {=LARGE(IF(AND(Array1+8<20,6-Array1<0),Array2,0),1)}
where: Array1 is a sequence of numbers, say, 2 5 8 11 14 17
Array2 is a corresponding sequence of numbers 1 2 3 4 5 6

the resulting array should return the numbers 0 0 3 4 0 0
and my answer should be 4
instead my answer is 0

when I break it down and select six cells and use the formula:
{=if(Array1+8<20,Array2,0)}
my result is: 1 2 3 4 0 0

and another six cells and use the formula: {=if(6-Array1<0,Array2,0)}
my result is: 0 0 3 4 5 6

when I select a single cell and use the formula: {=large(if(Array1+8<20,Array2,0),1)}
my result is: 4

when I select a single cell and use the formula: {=large(if(6-Array1<0,Array2,0),1)}
my result is: 6

I've tried this method several different ways, even using "Yes" & "No" as my result and then try to match them up. I've tried doing this not using named array and just selecting the cells themselves for the formula. All with the same results.

View 4 Replies View Related

Using Arrays In Vba

Jan 4, 2010

How do I deal with arrays in vba? Below is a dummy code just to check the array I specified is working:

View 5 Replies View Related

Using Arrays With VBA

Sep 8, 2007

I have a worksheet that has 20 columns and 500 rows. I created a VBA macro to loop through the data to hide rows that do not meet certain criteria. After the data is selected I copy and paste those selected rows to another sheet. The macro works well but I would like to use an array to contain the data that I copy and paste to a new sheet. I have been trying to find information on multi deminsion arrays but I have not been able to fully understand how to get the information into the array and then how to get it out again. Most of the examples that I have seen are for two maybe three columns (dimensions). I am hoping that someone could point me in the right direction to get started on this. I also have a few books on Exel VBA but none of them seem to address my question.

View 9 Replies View Related

Mod And Arrays

Nov 18, 2009

I am trying to fill calumn B with the data from an array. There are more rows than entries in my array and I want to loop back to the start of my array when it reaches the end of the array results.

Sub test()

Dim i As Integer, MyArray As Variant, RowCount As Integer, ArrayCount As Integer

MyArray = Array("test 1", "test 2", "test 3", "test 4", "test 5")
RowCount = Cells(Rows.Count, 1).End(xlUp).Row
ArrayCount = UBound(MyArray)

For i = 1 To RowCount
Range("B" & i).Value = MyArray(i - 1 Mod ArrayCount)
Next i

End Sub

View 9 Replies View Related

Sort / Filter Rows: Sort A Range Of Row

Nov 26, 2008

I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.

View 3 Replies View Related

Macro To Sort Colums And Protect After Sort

Nov 5, 2009

I am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.

If possible i would also like a macro to protect this range after the sort,

View 3 Replies View Related

Summing W/ Arrays

Jan 15, 2009

The formula is designed to sum a set of data based on 22 variables between a certain date range. In order to keep the formula manageable, I have grouped the variables into arrays.

EX.

Array 1. - 20 potential choices (Service codes)
Array 2. - 2 choices (pkg types)
Array 3. - 2 choices (volume type)
Array 4. - 3 choices (company names)

Each array (listed horizontally on one spreadsheet) calls a specific column of data to match from a different worksheet. I have no problem if only applying one array but multiple arrays return incorrect values.

View 9 Replies View Related

Get The Difference Between Two 2 D Arrays In VBA

Aug 7, 2013

I have two 2 Dimensional String Arrays with data. I need to find a way to get the difference between these two Arrays. I am new to VBA, I don't know how to deal with these. I certainly feel that there is some efficient function for doing this. or Is the naive two for lop concept is the only way to go?

View 2 Replies View Related

Constants As Arrays In Vba?

Apr 16, 2009

Can you declare a const as an array in VBA? For example, let's say I wanted to define an array of ORDINALS:

Const ORDINALS(0 to 9) = ("0th","1st","2nd","3rd","4th","5th","6th","7th","8th","9th")

View 2 Replies View Related

Cycling Through 2 Arrays

Jan 24, 2012

I have an array that will open specific workbooks. But Now I need to append a Case Number to the beginning of the file, and I don't know how to cycle through two Arrays. I have been setting it up as:

Code:
Dim Report
Dim Reports
Dim WB AS Workbook
Dim WS AS Worksheet

Reports = Array("Case01", "Case02", "Case03", "Case04")

For Each Report in Reports
Then I open the workbooks and print them.
Next Report

Now I want to add in a 2nd Array, that will Append the 1st item in CRN to Reports, then the 2nd item in CRN to 2nd item in Reports. So it would look something like this.

Code:
Dim Report
Dim Reports
Dim CRN
Dim CRNS
Dim WB AS Workbook
Dim WS AS Worksheet

CRNS = Array("0501202201", "0134851081", "9715288103", "1697774009")
Reports = Array("Case01", "Case02", "Case03", "Case04")

For each CRN in CRNS
For Each Report in Reports

Then I open the workbooks and print them.

Next Report
Next CRN

But that cycled through each CRN before moving to the next Report?

View 9 Replies View Related

Adding Up Two Arrays?

Jul 3, 2012

I have two arrays of the same size and shape. Question: is there a more straight forward way to add up all the corresponding elements in both arrays without looping through each pair of numbers one at a time?

View 2 Replies View Related

How To Concatenate Two Arrays

Jul 24, 2012

Currently I am trying to concatenate two arrays stockotherarray and stockfittingsarray to create stockarraynew

Below is my code, I keep receiving a compile error.

Sub stockarraynew()
stockarraynew() = Split(Join(stockotherarray & Join(stockfittingsarray))
End Function

View 9 Replies View Related

Formulas That Can Be Used With Arrays

Jun 12, 2007

Does anyone happen to know of a list of formulas in Excel that can be used as Array formulas?

I am trying to get a better grasp of what happens with array formulas in Excel - for instance MATCH seems to be a good candidate to hold an array of values when confirmed with CSE, but INDEX seems like a formula that wouldn't itself hold an array of values (although it might be able to lookup the corresponding cells of two arrays with corresponding rows and columns).

View 9 Replies View Related

Working With Arrays

Jun 16, 2007

Below is my attempt to understand working with arrays of values.

Test() works fine. Yay! But it is "too easy" just plugging in a hard value for the range address...I don't always have that luxury. Normally I work with ranges that I've determined at runtime. I'd like to assign a range of values to an array but my attempt below -- test2() -- fails. I am not even sure if my effort is a good approach or not, but I know it doesn't work!

Basically, I am looking for how to assign the range of values to an array when I am determining the range in code -- as in:

After


Set Range = ~~

or,


Range(Cells(1,1),Cells(x,y)) ~~................

View 9 Replies View Related

Combinations Of Arrays Using Vba

Apr 16, 2009

I need to find the possible combinations for several arrays consisting of binary data. I shall give a simplified example with three arrays and four fields. Lets assume the three starting arrays look as follows:

1 0 0 0
1 0 0 0
1 0 0 0

Each row may contain one field = "1" and all other fields = "0". So the next possible combinations may be:

1 0 0 0
1 0 0 0
0 1 0 0

1 0 0 0
1 0 0 0
0 0 1 0

etc...until:

0 0 0 1
0 0 0 1
0 0 0 1

Assuming k = number of fields and c = number of arrays the possible combinations are k ^ c = 4 ^ 3 = 64. I am looking for vba coding that allows to cycle through all possible combinations and displays them in an excel spreadsheet. Ideally, the code should work for any k and c. Could anyone please give me any pointers as to how to tackle this problem?

p.s. in essence I'm looking for the tabular representation of a tree plan with four branches to commence with, a total of 16 decision nodes, and two possible outcomes (1 and 0)

View 9 Replies View Related

(dynamic) Arrays In VBA

Apr 20, 2009

Well I find myself going round in circles so I thought I'd just ask hopefully solve things quicker.
I'm struggling with arrays in VBA, at some point I could use them but now it seems I've forgotten everything and even reading some tips on-line doesn't seem to be helping at this point.

I'd like to be able to use a dynamic 2-dimensional array to store values. I'm reading these in from a worksheet under certain conditions (depending on a value in another column) and that part works. I know I'll always have 2 columns but I don't know the amount of rows.
I'm calling a Sub that does an if-test and copies the valeus into the array, then I'd like to "reDim" the array 1 row larger for the next pass.

Column1 Column2
Text1 Value1
...
Text2 Value2
...
Text3 Value3
...
To get an array like so:

Arrayindex(i,1) Arrayindex(i,2)
Text1 Value1
Text2 Value2
Text3 Value3
Depending on how I make initial declarations I either end up with an "subscript out of range" or "array already dimensioned" error. So I'm apparently not getting the right syntax in the dim or redim?

View 9 Replies View Related

VBA - Arrays From Ranges

Dec 3, 2009

I am trying to loop through the values of an array that was populated from a range. Essentially I'm trying to manipulate a pivottable depending upon what a person selected in a control form. Below is my
Dim userWeekArray As Variant
userWeekArray = Sheets("Valid Values").Range("A20:A27")
For w = 1 To 8
If .PivotItems(w).Name = userWeekArray(w) Then
.PivotItems(w).Visible = True
Else
.PivotItems(w).Visible = False
End If
Next

I get an error message stating that Subscript is Out of Range. It seems Excel is assuming that userWeekArray is a Range object when I want it to be an Array. How can I populate an array with elements from a range in a worksheet so that I can then easily loop through the array to examine the elements?

View 9 Replies View Related

Clearing Arrays

Apr 9, 2005

I am inputting strings into an array and want to clear (i.e remove the contents of all the array) after i have finished witht the array.

View 9 Replies View Related

Referencing Arrays

Jun 15, 2006

I'm trying to do a loop through four arrays named Asc, Bsc, Csc and Dsc. I'm using the following code to do this but it comes up with a Type Mismatch error on the penultimate line. Please could someone explain the problem? I'm getting a bit confused as to whether I'm mixing up different types of variables, and not sure if CVar is the appropriate function.

For ch = 65 To 68
Sc = CVar(Chr(ch) & "sc")
For r = 1 To 3
If Sc(r, 1) = "" Then 'ERROR HERE
etc...

View 9 Replies View Related

Passing Arrays

Jun 16, 2006

Option Base 1
Dim Covariance(1 To 5, 1 To 5) As Double
Dim Mean(1 To 5) As Double
Dim Portfolio(1 To 10, 1 To 2, 1 To 5) As Double
Dim PortfolioValues(1 To 10, 1 To 2) As Double

And my main function:

Function Generate_Portfolio_Values()

Dim a As Integer, b As Integer, c As Integer
Dim m As Integer

Fill_Covariance_Array
Fill_Mean_Array
Fill_Portfolio_Array

View 5 Replies View Related

Arrays In Ranges

Jul 13, 2006

I want to have a dynamic range that will increment and return a value to a variant for some comparisons. Here is what i have

For i = 0 To 50
If [Q + i].Value <> "valid" Then
Goto GotLicenses
Else
varHostNumber(i) = [C + i].Value
MsgBox varHostNumber(i), vbOKOnly
End If
GotLicenses:
Next i

I want my if statment to use the variable "i" as part of the range for the comparison. Right now the msgbox is just to see what is happening, but will later become a different if statment that will blank a cell's value if it doesn't match the array variables.

View 3 Replies View Related

Modifiying Arrays

Jan 17, 2007

I have imported realtime currency prices into a spreadsheet using DDE links, if I have 5 rows showing the last 5 days prices the data is displayed with the most recent days record at the bottom of the table however I would like the most recent records to be displayed at the top.

ACTUAL RESULT
11/01/20071.9536
12/01/20071.9607
15/01/20071.9668
16/01/20071.9705
17/01/20071.9666

DESIRED RESULT
17/01/20071.9666
16/01/20071.9705
15/01/20071.9668
12/01/20071.9607
11/01/20071.9536

I have tried sorting the data by the date column, however because it is an array it will not allow this. I know I could paste link each row into another area of the spreadsheet so as to invert the data and maintain the DDE links; this is fine if I only have a few rows but if there are hundreds then it is a bit time consuming. I was wondering if there was a better method of inverting the data so that the most recent records appear at the top of the table.

View 3 Replies View Related

Compare 2 Different Arrays`

Jun 15, 2007

I need to compare 2 arrays which may not be the same length and then return values which are in one array and not the other.

View 9 Replies View Related

Looking For More Efficient Way To Activate Arrays?

Sep 17, 2012

I have a bunch of array formulas that need to be activated by going on the cell, hitting F2, then hitting Ctrl+Shift+Enter. In the attached sheet I have done a record macro to automate this. I have another sheet with something similar just a lot more items on Sheet1 (~250 items). It will be very time consuming for me to record macro and hit F2, then hit Ctrl+Shift+Enter ~250 times so I am wondering if there is any way to activate all the array formulas on the sheet in one go using 1 formula. I'm not too concerned with file size.Also, not all arrays only include columns A and C, other columns (D, G, H and J) are included too.

View 3 Replies View Related







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