A
Alan Beban
Steve said:
Unfortunately, I find the above incomprehensible.
Alan Beban
Unfortunately, I find the above incomprehensible.
Alan Beban
S
Steve
Leo,
Correction to my last sentence of that last posting, "Basically, I
needed each unique entity listed in a row to be re-routed into a
single column of "TC#" with their corresponding "CUST" and "SPEC"
beside them, i.e. transposing all of the data into a total of three
columns, "TC#", "CUST" and "SPEC". Any ideas on how to do this with
your formula would be greatly appreciated.
G
Guest
Spare us rhetorical questions!
That's classic Harlan folks. As rude and obnoxious as the
day is long.
Comes up with a mish-mash of trash that doesn't work then
he cuts and runs.
That's classic Harlan folks. As rude and obnoxious as the
day is long.
Comes up with a mish-mash of trash that doesn't work then
he cuts and runs.
H
Harlan Grove
No. Days are too short.
Uh, moron, did you happen to read my follow-up to the OP?
http://groups.google.com/[email protected]
Or did you miss it because you don't even know how to use CDO?
Since the OP hasn't responded, either the follow-up addressed the OP's
problem (which seems to have been due to not seeing the following statement
from my earlier response
http://groups.google.com/[email protected]
"the customer code sought in either a range or string expression named CC").
Then again, you never have let facts intrude upon your peculiar dementia, so
why start now?
L
Leo Heuser
Steve
With the new information, I realize, that it's quite a bit
of data to rearrange. Up to about 70000 instances of
my array formulae and with the size of the formula, I
don't think, that's the way to go. It will probably slow
Excel down to a crawl. Instead I have built a macro
for you to use, when rearranging the data. It's
extremely fast because data is transferred to an array,
processed in the array and put back in the workbook
at the end. With the amount of data you have, it will
probably do the job in seconds!
Edit the lines
SourceSheet = "Sheet1"
SourceCells = "A2:AG1300" 'Cust + Spec + all TcColumns. No headings.
DestSheet = "Sheet2"
DestCell = "A2" 'A1:C1 is used for headings
to reflect the actual setup of your data.
There's no column for FIND in SourceCells. CUST *must*
be in the first column, and SPEC *must* be in the second,
all other columns contain TC-data.
Bon voyage, and let me know how it worked on your
dataset
--
Best Regards
Leo Heuser
Excel MVP
Followup to newsgroup only please.
Sub RearrangeData()
'Leo Heuser, 31-10-2003
Dim Counter As Long
Dim DataRange As Range
Dim DataRangeValue As Variant
Dim DestSheet As String
Dim DestCell As String
Dim ElementsInTcRange As Long
Dim GetValue As Long
Dim Headings As Variant
Dim lColumn As Long
Dim lRow As Long
Dim NumberOfHeadings As Long
Dim ResultArray As Variant
Dim SourceCells As String
Dim SourceSheet As String
Dim TCRange As Range
SourceSheet = "Sheet1"
SourceCells = "A2:AG1300" 'Cust + Spec + all TcColumns. No headings.
DestSheet = "Sheet2"
DestCell = "A2" 'A1:C1 is used for headings
Headings = Array("FIND", "CUST", "SPEC")
Application.ScreenUpdating = False
Set DataRange = Sheets(SourceSheet).Range(SourceCells)
DataRangeValue = DataRange.Value
Set TCRange = DataRange.Columns(3). _
Resize(DataRange.Rows.Count, DataRange.Columns.Count - 2)
ElementsInTcRange = Application.WorksheetFunction.CountA(TCRange)
NumberOfHeadings = UBound(Headings) - LBound(Headings) + 1
ReDim ResultArray(1 To ElementsInTcRange, 1 To 3)
For lRow = LBound(DataRangeValue, 1) To UBound(DataRangeValue, 1)
For lColumn = 3 To UBound(DataRangeValue, 2)
If Not IsEmpty(DataRangeValue(lRow, lColumn)) Then
GetValue = GetValue + 1
ResultArray(GetValue, 1) = DataRangeValue(lRow, lColumn)
ResultArray(GetValue, 2) = DataRangeValue(lRow, 1)
ResultArray(GetValue, 3) = DataRangeValue(lRow, 2)
End If
Next lColumn
Next lRow
With Worksheets(DestSheet)
.Activate
With .Range(DestCell)
With .Offset(-1, 0).Resize(1, NumberOfHeadings)
.Value = Headings
.Font.Bold = True
End With
.Resize(GetValue, 3).Value = ResultArray
.Sort _
key1:=Range(DestCell), _
header:=xlYes, _
order1:=xlAscending
End With
End With
Application.ScreenUpdating = True
End Sub
With the new information, I realize, that it's quite a bit
of data to rearrange. Up to about 70000 instances of
my array formulae and with the size of the formula, I
don't think, that's the way to go. It will probably slow
Excel down to a crawl. Instead I have built a macro
for you to use, when rearranging the data. It's
extremely fast because data is transferred to an array,
processed in the array and put back in the workbook
at the end. With the amount of data you have, it will
probably do the job in seconds!
Edit the lines
SourceSheet = "Sheet1"
SourceCells = "A2:AG1300" 'Cust + Spec + all TcColumns. No headings.
DestSheet = "Sheet2"
DestCell = "A2" 'A1:C1 is used for headings
to reflect the actual setup of your data.
There's no column for FIND in SourceCells. CUST *must*
be in the first column, and SPEC *must* be in the second,
all other columns contain TC-data.
Bon voyage, and let me know how it worked on your
dataset
--
Best Regards
Leo Heuser
Excel MVP
Followup to newsgroup only please.
Sub RearrangeData()
'Leo Heuser, 31-10-2003
Dim Counter As Long
Dim DataRange As Range
Dim DataRangeValue As Variant
Dim DestSheet As String
Dim DestCell As String
Dim ElementsInTcRange As Long
Dim GetValue As Long
Dim Headings As Variant
Dim lColumn As Long
Dim lRow As Long
Dim NumberOfHeadings As Long
Dim ResultArray As Variant
Dim SourceCells As String
Dim SourceSheet As String
Dim TCRange As Range
SourceSheet = "Sheet1"
SourceCells = "A2:AG1300" 'Cust + Spec + all TcColumns. No headings.
DestSheet = "Sheet2"
DestCell = "A2" 'A1:C1 is used for headings
Headings = Array("FIND", "CUST", "SPEC")
Application.ScreenUpdating = False
Set DataRange = Sheets(SourceSheet).Range(SourceCells)
DataRangeValue = DataRange.Value
Set TCRange = DataRange.Columns(3). _
Resize(DataRange.Rows.Count, DataRange.Columns.Count - 2)
ElementsInTcRange = Application.WorksheetFunction.CountA(TCRange)
NumberOfHeadings = UBound(Headings) - LBound(Headings) + 1
ReDim ResultArray(1 To ElementsInTcRange, 1 To 3)
For lRow = LBound(DataRangeValue, 1) To UBound(DataRangeValue, 1)
For lColumn = 3 To UBound(DataRangeValue, 2)
If Not IsEmpty(DataRangeValue(lRow, lColumn)) Then
GetValue = GetValue + 1
ResultArray(GetValue, 1) = DataRangeValue(lRow, lColumn)
ResultArray(GetValue, 2) = DataRangeValue(lRow, 1)
ResultArray(GetValue, 3) = DataRangeValue(lRow, 2)
End If
Next lColumn
Next lRow
With Worksheets(DestSheet)
.Activate
With .Range(DestCell)
With .Offset(-1, 0).Resize(1, NumberOfHeadings)
.Value = Headings
.Font.Bold = True
End With
.Resize(GetValue, 3).Value = ResultArray
.Sort _
key1:=Range(DestCell), _
header:=xlYes, _
order1:=xlAscending
End With
End With
Application.ScreenUpdating = True
End Sub
H
Harlan Grove
Acronym for 'lots of luck', an expression of hopelessness, as in the chances
that you'll ever contribute anything worthwhile in this newsgroup or in the
wider world.
Indeed, LOL.
S
Steve
Leo,
I tried the macro just on my example that I had used, but must have
done something wrong because when I tried to run it nothing happened
to the destination sheet.
Here is what it looked like, maybe you can spot what was wrong with
it:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/1/2003 by Steve
SourceSheet = "Sheet1"
SourceCells = "A2:H10" 'Cust + Spec + all TcColumns. No headings.
DestSheet = "Sheet2"
DestCell = A2 'A1:C1 is used for headings
'
End Sub
Regards,
Steve
I tried the macro just on my example that I had used, but must have
done something wrong because when I tried to run it nothing happened
to the destination sheet.
Here is what it looked like, maybe you can spot what was wrong with
it:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/1/2003 by Steve
SourceSheet = "Sheet1"
SourceCells = "A2:H10" 'Cust + Spec + all TcColumns. No headings.
DestSheet = "Sheet2"
DestCell = A2 'A1:C1 is used for headings
'
End Sub
Regards,
Steve
S
Steve
Leo,
You are totally awesome...this is exactly what I was looking for...
It worked wonderfully.
Thanks again,
Steve
You are totally awesome...this is exactly what I was looking for...
It worked wonderfully.
Thanks again,
Steve
L
Leo Heuser
You're welcome, Steve.
Thanks for the feedback! It's appreciated.
LeoH
Thanks for the feedback! It's appreciated.
LeoH
S
Steve
Leo,
You can scratch this posting that I had started, I thought I had
stopped it in time. It was just after this when I had realized that
there was a lot more to the macro in the posting that you had put
together that was part of a continuation to that screen. Again, it
worked beautifully.
Thanks again,
Steve
S
Steve
Harlan,
Sorry about not getting back to you sooner...busy with kids and
halloween..
Anyway, I think I'm almost there with your formula. I wanted to see
if I could get everyone's solutions to work one way or the other. I
went ahead and named the range "A2:A10" with "CC" as you mentioned,
but now I get the following results in "N01:O13"
MMM MMM
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
Obviously I'm still screwing it up somewhere...Any ideas?
Also, sorry about the negative outside interference, I don't believe
all that is necessary unless they have something contstructive to add,
maybe that's why they remain anonymous
Regards,
Steve
Sorry about not getting back to you sooner...busy with kids and
halloween..
Anyway, I think I'm almost there with your formula. I wanted to see
if I could get everyone's solutions to work one way or the other. I
went ahead and named the range "A2:A10" with "CC" as you mentioned,
but now I get the following results in "N01:O13"
MMM MMM
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
Obviously I'm still screwing it up somewhere...Any ideas?
Also, sorry about the negative outside interference, I don't believe
all that is necessary unless they have something contstructive to add,
maybe that's why they remain anonymous
Regards,
Steve
S
Steve
Alan,
Sorry, for not getting back sooner, busy weekend here... Well I did
manage to get your add-in file into my workbook and try out the
formula. Here is what I got as a result:
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
I ended up with the same results for all 9 rows, which ended up being
the "SPEC" for the second instance that "CU284" (in the formula)
showed up in the array (i.e. row 1 has "SPEC" 24409 and row 4 has
"SPEC" 24467). I never got any of the #N/A results that you had
mentioned. I must still have something not quite right in my
worksheet. Any ideas, because I really would like to see how this one
works when I've done it right?
Here is the formula I used for the first column:
=OFFSET(INDIRECT(ArrayMatch("CU284",$C$2:$H$10,"A",4)),0,-(ColumnVector(ArrayMatch("CU284",$C$2:$H$10),2))-1-(1-COLUMN(A1)))
FOR This Data:
AAA 24409 CU281 CU282 CU283 CU284 CU285 CU286
VVV 24444 CA952
WWW 24435 CU024
AAA 24467 CU281 CU282 CU283 CU284 CU285 CU286
VVV 24488 CA952 CA954 CA977
YYY 24463 CU210
OOO 24498 CU227 CU289
MMM 24434 CU241 CU242 CU244 CU248
PPP 24427 CU254
Regards,
Steve
Sorry, for not getting back sooner, busy weekend here... Well I did
manage to get your add-in file into my workbook and try out the
formula. Here is what I got as a result:
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
I ended up with the same results for all 9 rows, which ended up being
the "SPEC" for the second instance that "CU284" (in the formula)
showed up in the array (i.e. row 1 has "SPEC" 24409 and row 4 has
"SPEC" 24467). I never got any of the #N/A results that you had
mentioned. I must still have something not quite right in my
worksheet. Any ideas, because I really would like to see how this one
works when I've done it right?
Here is the formula I used for the first column:
=OFFSET(INDIRECT(ArrayMatch("CU284",$C$2:$H$10,"A",4)),0,-(ColumnVector(ArrayMatch("CU284",$C$2:$H$10),2))-1-(1-COLUMN(A1)))
FOR This Data:
AAA 24409 CU281 CU282 CU283 CU284 CU285 CU286
VVV 24444 CA952
WWW 24435 CU024
AAA 24467 CU281 CU282 CU283 CU284 CU285 CU286
VVV 24488 CA952 CA954 CA977
YYY 24463 CU210
OOO 24498 CU227 CU289
MMM 24434 CU241 CU242 CU244 CU248
PPP 24427 CU254
Regards,
Steve
A
Alan Beban
Steve said:
In what cells do you have that data?
Alan Beban
S
Steve
Alan,
The data is in columns A through H and rows 1 through 9. Unless I put
the column labels back in which would then make it rows 2 through 10.
Thus, if I were to add a "FIND" column under A, then everything would
shift to the right, being columns B through I. Below is exactly what
I ended up with when I was able to start seeing some results from the
modified formula I sent in my last posting...
Thanks,
Steve
The data is in columns A through H and rows 1 through 9. Unless I put
the column labels back in which would then make it rows 2 through 10.
Thus, if I were to add a "FIND" column under A, then everything would
shift to the right, being columns B through I. Below is exactly what
I ended up with when I was able to start seeing some results from the
modified formula I sent in my last posting...
Thanks,
Steve
A
Alan Beban
In your modified formula change $C$2:$H$10 to $C$1:$H:$9 (twice) and, as
I originally indicated way back when, *array enter it* into the first
column, then fill across one more column.
Alan Beban
I originally indicated way back when, *array enter it* into the first
column, then fill across one more column.
Alan Beban
S
Steve
Alan,
I must still be doing something wrong, because I still get the same
results, except now the "CU284" in your formula only gives me "#REF!",
but if I change it to one of the "TC" numbers in the other rows I get
something similar to what I had said I got in my last posting, (see
below). Perhaps, I'm not doing the "*array enter it*" thing properly.
Am I missing something?
Please advise,
Steve
By changing "CU284" in your formula to "CA977", I got:
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
I must still be doing something wrong, because I still get the same
results, except now the "CU284" in your formula only gives me "#REF!",
but if I change it to one of the "TC" numbers in the other rows I get
something similar to what I had said I got in my last posting, (see
below). Perhaps, I'm not doing the "*array enter it*" thing properly.
Am I missing something?
Please advise,
Steve
By changing "CU284" in your formula to "CA977", I got:
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488