Posts by robystar



Based on the assumptions made in previous message and provided the worksheet structure remains coherent ...
attached is your test file with a macro ...
Hope this will help
I would say this worked exactly as I had hoped. It took about 30 seconds to complete through an adjusted range of 22737 rows. I only received (2) Value#! errors likely due to inconsistent formatted data. Not worth addressing with error handling. Thank you so much for your effort.

I have done so. I think it looks more chaotic than it is. If the data were randomly placed then I think this would be a somewhat hopeless effort. My thinking that the data extraction is even possible is the consistency between the data sets. The JSN code is always preceded by the JSN title followed by a consistent number of spaces. The BTU / Hour data is always located in a single cell starting the text "Utilities" the same number of cells down from the JSN number. I'm thinking if the JSN number can be matched then the BTU / Hour value fan be located consistently by finding the string in the cell offset from the JSN cell.

Okay, I understand. See attached CSV file with txt extension containing the RAWDATA only. It's very messy, but should work for referencing the relative location for the data. This information is extracted from a U.S. Military Standard document so you can expect most of the information to be at least 10 years behind the modern world lol.

Hi,
Since you are very probably using Excel 365, would you attaching a version of your Test file in which, beforehand, you would have "CopyPaste Values" in your tab JSN_FILTER
This would allow to see the point you have already reached ... in your "ExtractionSaga"
See attached with JSN_CopyPasteData tab containing just the filtered values that I have been able to extract so far. Hopefully I've interpreted your request correctly. "ExtractionSaga" is the correct term.

Attached is a shortened version of the imported dataset. Please note how the data in the RAWDATA tab is grouped with multiple labels and values in a single cell.
In the JSN_FILTER tab I need to find the matching JSN code in the RAWDATA which is located in a single cell along with miscellaneous data. Once this code is located I need to obtain the "BTU / Hour" data. This data is located is single cell along with miscellaneous data as well. The "BTU / Hour" text here is followed by (5) spaces. Unfortunately, the "BTU / Hour" data is inconsistent. It's sometime 0, blank, or a (3) or (4) digit number. A blank should be interpreted as 0. Any help would be greatly appreciated.

Re: Find Row Value in Array Given Column and Data Value
Quote from Wisa;764251Hi robystar,
I'm not able to completely match your outputs, can you double check these are correct? In particular 7 & 3069 seems like it should return G?
I've written some formulae here which should at least point you in the right direction.
[ATTACH=CONFIG]68129[/ATTACH]
B7 =IFERROR(MATCH(A7,$B$1:$E$1,1),0)+1*ISNA(MATCH(A7,$B$1:$E$1,0))
B8 =IFERROR(MATCH(A8,INDEX($B$2:$E$5,,B7),1),0)+1*ISNA(MATCH(A8,INDEX($B$2:$E$5,,B7),0))
B9 =INDEX($A$2:$A$5,B8)Wisa, thanks! You are absolutely correct. The sample output should be G. Thanks for catching that. Your formulae work. I will play around with this and post if I can make any improvements. Thank you very much for taking the time to assist. It is very much appreciated.

Hello,
I would like some assistance in generating and excel formula to look find the row value given the column value and data value. The column and data value used in finding the row value will be greater than or equal to the input column and data values. The following is sample table data:[TABLE="class: grid, width: 500"]
[tr][td][/td][td]1
[/td][td]3
[/td][td]7
[/td][td]8
[/td][/tr][tr][td]A
[/td][td]750
[/td][td]575
[/td][td]480
[/td][td]410
[/td][/tr][tr][td]C
[/td][td]2200
[/td][td]1760
[/td][td]1470
[/td][td]1260
[/td][/tr][tr][td]G
[/td][td]4600
[/td][td]3680
[/td][td]3070
[/td][td]2630
[/td][/tr][tr][td]D
[/td][td]8650
[/td][td]6920
[/td][td]5765
[/td][td]4945
[/td][/tr]
[/TABLE]Sample input would be 3.5 for the column and 1500 for the data. The output would be row C. Since column is > 3 then go to column 7, and since data > 575 the go to data 1760. More sample input output:
4 & 1800 = G
7 & 3069 = C
7.9 & 410 = A
0.9 & 8651 = DAny help would be greatly appreciated.

Re: Update on IndentLevel Change
Quote from Ger Plante;762266At the moment your code is running from Worksheet_change event... which means that your code is only triggered when the sheet changes... (like typing something into a cell AND hitting enter). You can put your IndentFormat against of the predefined events in Excel... you just have to know what the event is and make sure that that event fires or is triggered when you click on "indent" in the toolbar. The bad news is that no event that I know is triggered by clicking on the indent button. So I think you have two options now... well three maybe...
1) Dont do anything  what you have is good enough
2) Run the code as a standalone macro, or from your own button on a sheet
3) Change the code to run from some other event, like for example, Worksheet_calculate (which is triggered everytime the worksheet has to recalculate its formulas), or say selection_change (which happens each time you click to, or move to another cell).With option 3, you do need to be careful though as it might slow down your code, and you may need to restrict your code from running only in some circumstances.
Hope this helps
GerGer, thanks for the feedback. Out of curiosity I did try using the SelectionChange event and did notice a slow down. I suppose I should leave well enough alone. Thanks for the suggestions! Rob

Hello,
Here is the code I'm using below:Code
Display More... For RowCnt = 7 To LastRow 'ADD SPECIAL CHARACTERS BASED ON INDENT LEVEL Select Case Cells(RowCnt, 2).IndentLevel Case 0 If AscW(Left(Cells(RowCnt, 2).Value, 1)) <> 9660 Then Cells(RowCnt, 2).Value = ChrW(9660) & " " & Cells(RowCnt, 2).Value End If fColor = RGB(31, 73, 125) Case 1 If AscW(Left(Cells(RowCnt, 2).Value, 1)) <> 9658 Then Cells(RowCnt, 2).Value = ChrW(9658) & " " & Cells(RowCnt, 2).Value End If fColor = RGB(153, 204, 255) Case 2 If AscW(Left(Cells(RowCnt, 2).Value, 1)) <> 9632 Then Cells(RowCnt, 2).Value = ChrW(9632) & " " & Cells(RowCnt, 2).Value End If Case Is >= 3 If AscW(Left(Cells(RowCnt, 2).Value, 1)) <> 9643 Then Cells(RowCnt, 2).Value = ChrW(9643) & " " & Cells(RowCnt, 2).Value End If Cells(RowCnt, 2).IndentLevel = 4 End Select Next RowCnt ...
This code formats the cells based on its indent level and adds a special character if none exists as the first character in the string.
I'm calling the code from the sheet using:
The issue is that even if a number of cells are indented using the excel toolbar menu then the cell formatting is not updated until a change is made (i.e. any cell valued edited). Is there a way to have the cells update automatically for the changes to take effect? Any help would be greatly appreciated. Thanks! Rob

Re: Sum from match to last row
Quote from Carim;761766An attempt within a single formula ...
=SUMPRODUCT(((OFFSET(A1,MATCH("a",A1:A20,0)1,0):A10="a")+(OFFSET(A1,MATCH("a",A1:A20,0)1,0):A10="x"))*(OFFSET(B1,MATCH("a",A1:A20,0)1,0):B10))
Obviously "a" has to be replaced by a cell containing "a" or "b" or "c" ... since "x" seems to be fixed ...
Does it produce the expected result ?
Excellent, this checks out in all cases. I can follow along, but I don't quite understand whats going on with the [A10="a"] part of the formula. This does exactly what I need. Much thanks to you Carim and happy holidays!!!

Re: Sum from match to last row
Quote from Carim;761763A basic UDF could like the following :
Code
Display MoreFunction RobySum(init As String, rng As Range) Dim x As Long Dim y As Long Dim i As Long x = Application.Match(init, rng, 0) y = Range("A65536").End(xlUp).Row For i = x To y If Cells(i, 1).Value = init Or Cells(i, 1).Value = "x" Then RobySum = RobySum + Cells(i, 2) End If Next i End Function
Hopefully, the result is correct ...
Now, for a nonUDF solution ... do you mind if there is a helper column ?
HTH
Thanks Carim. I've been playing around with several helper columns to get this to work so I do not mind in the least.

Re: Sum from match to last row
Quote from Carim;761761Hi,
Sorry I did not realize there was one more condition ... the non matching rows ...
Your life would be easier with a UDF ... :smile:
Are you familiar enough with macros ... or would you like an example ...?
No problem. I appreciate the offer, but I can usual grind out a solution with macros. I just prefer to not use them if this sheet has the potential to make it into the wild If you think there is a way to accomplish this with a nonUDF then any assistance would be greatly appreciated.

Re: Sum from match to last row
Quote from Carim;761710Hi,
You could test following formula :
=SUM(INDIRECT(ADDRESS(MATCH("b",A1:A20,0),2,4)):INDIRECT("B"&SUMPRODUCT(MAX((ROW(B1:B20))*(B1:B20<>"")))))
HTH
Carim, thanks for the response. This is very close to what I'm looking for. However, I can't figure out how to not sum all the nonmatching rows below. I thought there may have been and easy way to do this with formulas, but I may resort to some VBA coding.

Re: Sum from match to last row
I figured out a portion of it with a bit of digging:
=SUM(SUMIF(A1:A10,{"b";"x"},B1:B10))
Similar to using SUMIF with an OR statement.
Not sure how to sum from the first match in a column.

Hello, I'm trying to come up with a function to sum the from a match in the first column down to the last row, summing a second match starting from the row of the first match, using the following sample data:
[TABLE="class: grid, width: 50, align: left"]
[tr][td]a
[/td][td]10
[/td][/tr][tr][td]a
[/td][td]5
[/td][/tr][tr][td]x
[/td][td]9
[/td][/tr][tr][td]b
[/td][td]8
[/td][/tr][tr][td]b
[/td][td]20
[/td][/tr][tr][td]x
[/td][td]21
[/td][/tr][tr][td]x
[/td][td]7
[/td][/tr][tr][td]c
[/td][td]11
[/td][/tr][tr][td]x
[/td][td]3
[/td][/tr][tr][td]x
[/td][td]12
[/td][/tr]
[/TABLE]Sample input/output would be:
Find "a" & "x" and Sum=10+5+9+21+7+3+12
Find "b" & "x" and Sum=8+20+21+7+3+12
Find "c" & "x" and Sum=11+3+12I thought this would be simple, but it's hurting my brain a bit. Any help would be greatly appreciated.  Rob

Re: Converting a sizing chart to excel
Quote from mikerickson;761555If that data is in A1:G13
and the Length is in J1 and the volume in K1Then 7COUNTIF($B$1:$G$1,">="&J1) is the number of the appropriate column.
and INDEX($B$2:$G$13, ,L1) is the entries in that column.so 13COUNTIF(INDEX($B$2:$G$13, ,7COUNTIF($B$1:$G$1,">="&J1)),">="&K1) is the number of the appropriate row
and INDEX($A$2:$A$13, 13COUNTIF(INDEX(Data, ,7COUNTIF($B$1:$G$1,">="&J1)),">="&K1), 1) is the desired result
Brilliant! I would have not thought to use the countif function in this case. A logical solution that I can follow. Thank you very much for taking the time to assist me with this. Much appreciated mikerickson!  Rob

Re: Converting a sizing chart to excel
Quote from mikerickson;761534Are the indices random?
Are the volumes the random part or is it everything.I was thinking that CEILING(length, 10) would help you find the column in question, but if the lengths aren't all multiples of 10, that won't work.
Similarly are the results all multiples of .5?
Are the lengths and results sorted ascending?I don't think that will work. Here is the the complete table I'm working with. It's a gas pipe sizing chart and doesn't appear to uniform in any direction. It seems like it should be a simple index/match formula, but I'm going in circles trying to figure it out.
[TABLE="class: grid, width: 500"]
[tr][td][/td][td]10
[/td][td]20
[/td][td]40
[/td][td]80
[/td][td]150
[/td][td]300
[/td][/tr][tr][td]1/2
[/td][td]120
[/td][td]85
[/td][td]60
[/td][td]42
[/td][td]31
[/td][td]22
[/td][/tr][tr][td]3/4
[/td][td]272
[/td][td]192
[/td][td]136
[/td][td]96
[/td][td]70
[/td][td]50
[/td][/tr][tr][td]1
[/td][td]547
[/td][td]387
[/td][td]273
[/td][td]193
[/td][td]141
[/td][td]100
[/td][/tr][tr][td]11/4
[/td][td]1200
[/td][td]849
[/td][td]600
[/td][td]424
[/td][td]310
[/td][td]219
[/td][/tr][tr][td]11/2
[/td][td]1860
[/td][td]1316
[/td][td]930
[/td][td]658
[/td][td]480
[/td][td]340
[/td][/tr][tr][td]2
[/td][td]3759
[/td][td]2658
[/td][td]1880
[/td][td]1330
[/td][td]971
[/td][td]686
[/td][/tr][tr][td]21/2
[/td][td]6169
[/td][td]4362
[/td][td]3084
[/td][td]2189
[/td][td]1593
[/td][td]1126
[/td][/tr][tr][td]3
[/td][td]11225
[/td][td]7938
[/td][td]5613
[/td][td]3969
[/td][td]2898
[/td][td]2049
[/td][/tr][tr][td]4
[/td][td]23479
[/td][td]16602
[/td][td]11740
[/td][td]8301
[/td][td]6062
[/td][td]4287
[/td][/tr][tr][td]5
[/td][td]42945
[/td][td]30367
[/td][td]21473
[/td][td]15183
[/td][td]11088
[/td][td]7841
[/td][/tr][tr][td]6
[/td][td]69671
[/td][td]49265
[/td][td]34836
[/td][td]24632
[/td][td]17989
[/td][td]12720
[/td][/tr][tr][td]8
[/td][td]141832
[/td][td]100290
[/td][td]70916
[/td][td]50145
[/td][td]36621
[/td][td]25895
[/td][/tr]
[/TABLE] 
Re: Converting a sizing chart to excel
Thanks for the response mikerickson. Unfortunately, this is just sample data. The actually chart data is seemingly random. I can handle the errors, but the matching and lookup portion is problematic for me.