Need some pro-Excel help

Conan257

Conan257

Conan257

Associate
Joined
8 Nov 2008
Posts
1,078
Location
Lincoln
I'm trying to overcome an issue in Excel that is generated by an output (which creates the .xlsx, but I can't get it fixed in that step even through it would probably be the best option). I'm hoping I could create a script within Excel to fix my issue, however it isn't my forte...

So I have 4 cells in each row that I'm working with, and about 150 rows. Each cell contains a string of values, the first text (letters & numbers) and the other 3 just numbers. The values within each cell are separated by commas (csv in a cell) and each position in the cell equates to the same position in the other 3 cells.

I need a system that looks at the third cell (A3, for example), finds the largest value, takes note of the position of this value in the string, and pulls the values from all four cells for that position and adds them somewhere else (so A1:A4 becomes a single position from the strings in A5:A8)....

Unable to provide the data, but an example could be:

A1: Alpha, Bravo, Charlie, Delta, Echo
A2: 1,2,3,4,5
A3: 4,7,2,5,6
A4: 100,200,300,400,500

In this example, the output would be:

A5: Bravo
A6: 2
A7: 7
A8: 200

Any thoughts?!
 
Can you not just use Text to Columns (basically splits comma seperated values into their own cells). That way, A1:E1 would be populated with the individual values currently all contained in A1. Same for row 2, 3, etc
 
I’m assuming that the number of values in each cell is variable, so a blanket text to columns won’t work.

It seems to me that you need to create a new sheet from the input and operate on that, then bring the desired values into the old sheet or a third sheet.

So you want a little macro.

For each column
Create temp worksheet.
Copy column from base sheet into column A of temp sheet.
Expand column A with text to columns or similar.
Find the max value in Row 1
Use that to find values.
Copy values into results are of base sheet or third sheet.
Delete temp worksheet.

But I think you would be better off reworking the design of your basic worksheet and you should start by ensuring that your data is normalised. Specifically into Third Normal Form. I think you want a set of 4 sheets, with each worksheet containing the expanded results of one row. You then have a fifth spreadsheet with the results. I’m way rusty on this stuff so I could be very wrong.
 
Conan257 said:
I'm trying to overcome an issue in Excel that is generated by an output (which creates the .xlsx, but I can't get it fixed in that step even through it would probably be the best option). I'm hoping I could create a script within Excel to fix my issue, however it isn't my forte...

So I have 4 cells in each row that I'm working with, and about 150 rows. Each cell contains a string of values, the first text (letters & numbers) and the other 3 just numbers. The values within each cell are separated by commas (csv in a cell) and each position in the cell equates to the same position in the other 3 cells.

I need a system that looks at the third cell (A3, for example), finds the largest value, takes note of the position of this value in the string, and pulls the values from all four cells for that position and adds them somewhere else (so A1:A4 becomes a single position from the strings in A5:A8)....

Unable to provide the data, but an example could be:

A1: Alpha, Bravo, Charlie, Delta, Echo
A2: 1,2,3,4,5
A3: 4,7,2,5,6
A4: 100,200,300,400,500

In this example, the output would be:

A5: Bravo
A6: 2
A7: 7
A8: 200

Any thoughts?!
Click to expand...
can you use VBA ?

can you run the following and check that it is picking the highest value from all the cells in row 3
this will start of in A3 and work to the right until it finds an empty cell
the rest is very easy but need to know if this works

The script assumes that there are only numbers and commas in row 3, spaces or letters will cause it to fail.

Code: 
Sub SplitText()

Dim vals() As String
Dim mVal

Range("A3").Select
Do Until ActiveCell = ""
    vals() = Split(ActiveCell, ",")
    mVal = 0
    For Count = 0 To UBound(vals)
        If vals(Count) / 1 > mVal Then
            mVal = vals(Count)
        End If
    Next Count
    ActiveCell.Offset(4, 0) = mVal
    ActiveCell.Offset(0, 1).Select
Loop

End Sub
 
Last edited:
Have to say VBA is probably the way to go here if you cannot do this in SQL.

However, I managed to make a formula which can figure out the how many'ith the largest number is. The index in the CSV array, that is.
It is very long as Excel formulas tend to be.
This is with the list of numbers in A6:
41,71,12,5,6
Code: 
=LEN(LEFT(A6,FIND(MAX(FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[1]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[2]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[3]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[4]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[5]")),A6) + LEN(MAX(FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[1]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[2]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[3]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[4]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[5]"))) - 1)) - LEN(SUBSTITUTE(LEFT(A6,FIND(MAX(FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[1]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[2]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[3]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[4]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[5]")),A6) + LEN(MAX(FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[1]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[2]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[3]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[4]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[5]"))) - 1), ",", "")) + 1

I said it quickly gets messy.

Obviously like all complex Excel formulas it was done one step at a time, but Excel's show formulas view, CTRL+`, quickly gets far too wide.

The FilterXML part is fixed on how many elements so if A6 had more than 5 elements, it would have to be extended.

It really is fixed at 5 elements, too few and the formula crashes, too many and it won't search through them

And this is just the first part.

A formula which takes that index and extracts the other parts would be quite complex too.
 
KompuKare said:
Have to say VBA is probably the way to go here if you cannot do this in SQL.

However, I managed to make a formula which can figure out the how many'ith the largest number is. The index in the CSV array, that is.
It is very long as Excel formulas tend to be.
This is with the list of numbers in A6:
41,71,12,5,6
Code: 
=LEN(LEFT(A6,FIND(MAX(FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[1]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[2]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[3]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[4]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[5]")),A6) + LEN(MAX(FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[1]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[2]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[3]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[4]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[5]"))) - 1)) - LEN(SUBSTITUTE(LEFT(A6,FIND(MAX(FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[1]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[2]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[3]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[4]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[5]")),A6) + LEN(MAX(FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[1]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[2]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[3]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[4]"), FILTERXML("<t><s>" & SUBSTITUTE( A6, ",", "</s><s>")&"</s></t>", "//s[5]"))) - 1), ",", "")) + 1

I said it quickly gets messy.

Obviously like all complex Excel formulas it was done one step at a time, but Excel's show formulas view, CTRL+`, quickly gets far too wide.

The FilterXML part is fixed on how many elements so if A6 had more than 5 elements, it would have to be extended.

It really is fixed at 5 elements, too few and the formula crashes, too many and it won't search through them

And this is just the first part.

A formula which takes that index and extracts the other parts would be quite complex too.
Click to expand...
I'm impressed, that is the longest cell formula I think i have ever seen (1643 characters) . When formula's get to about 6 inches they start to fry my brain and I turn to VBA. Yours is close on 12 foot. - LOL
 
SBK said:
I'm impressed, that is the longest cell formula I think i have ever seen (1643 characters) . When formula's get to about 6 inches they start to fry my brain and I turn to VBA. Yours is close on 12 foot. - LOL
Click to expand...
Well, of course it started one section at a time since Excel formulas haven't got variables, you end up having to repeat lots of bits.
If the OP's Excel isn't locked down, VBA is the way to go. Or at least make the wo custom VBA functions. One to get the ones of the largest value, and another to retrieve there n'th element of a CSV cell.
 
Thanks for the suggestions, all, and apologies for the delay....

As the data in these cells is related to an entry in column C, I've created a lookup table with the data for columns O,P,Q,R that I need to manipulate and am calling that data in by hlookup....
 
You must log in or register to reply here.
Back
Top Bottom