| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
search and return adjacent value
I have a file as follows
3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
how is this data layed out? All in one cell?
FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
Yes, it is a comma delimited file which imports into onecell per row in
Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
hi,
would be better. where do you want the results? next sheet? "JBS" wrote: Yes, it is a comma delimited file which imports into onecell per row in Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
Yes please, or in column A and B of the existing sheet if that is easier?
"FSt1" wrote: hi, would be better. where do you want the results? next sheet? "JBS" wrote: Yes, it is a comma delimited file which imports into onecell per row in Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
Try this in B1 and drag down.
=MID(A1,FIND(4103,A1,1)+5,(FIND(",",A1,FIND(4103,A 1,1)+5))-(FIND(4103,A1,1)+5)) Mike "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
hi,
About half done. need to adjust based on your last post and add loop. hold on. working. how big is the import? FSt1 "JBS" wrote: Yes please, or in column A and B of the existing sheet if that is easier? "FSt1" wrote: hi, would be better. where do you want the results? next sheet? "JBS" wrote: Yes, it is a comma delimited file which imports into onecell per row in Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
hi.
sorry it took so long. I add text to columns. use if you want. macro adds a sheet named results. edit to fit your data. Sub findstuff() 'Sheets("Sheet1").Select 'Range("A2:A4000").Select 'Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1),Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True 'Cells.AutoFit Sheets.Add.Name = "Results" Sheets("Sheet1").Select 'change if needed Dim f As String Dim ba As Range Dim bb As Range Dim bc As Range Dim bd As Range Dim r As Range Set ba = Range("B2") Set bc = Sheets("Results").Range("A2") 'destination 1 Application.CutCopyMode = False ba.Copy bc.PasteSpecial xlPasteAll f = InputBox("Enter something to find.") Do While Not IsEmpty(ba) Set bb = ba.Offset(1, 0) Set bd = bc.Offset(1, 0) ba.Copy bc.PasteSpecial xlPasteAll On Error Resume Next Set sr = Range(ba.Offset(0, -1), ba.Offset(0, -1).End(xlToRight)) sr.Select If f < "" Then Set r = Nothing Set r = sr.Find(What:=f, After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End If If r Is Nothing Then Set bc = Nothing Else Application.CutCopyMode = False r.Offset(0, 1).Copy bc.Offset(0, 1).PasteSpecial xlPasteAll End If Set ba = bb Set bc = bd Loop End Sub test on your test data. works on xp2003 regards FSt1 "FSt1" wrote: hi, About half done. need to adjust based on your last post and add loop. hold on. working. how big is the import? FSt1 "JBS" wrote: Yes please, or in column A and B of the existing sheet if that is easier? "FSt1" wrote: hi, would be better. where do you want the results? next sheet? "JBS" wrote: Yes, it is a comma delimited file which imports into onecell per row in Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
search and return adjacent value
Thank you. Will test today. Thanks again for your time
"FSt1" wrote: hi. sorry it took so long. I add text to columns. use if you want. macro adds a sheet named results. edit to fit your data. Sub findstuff() 'Sheets("Sheet1").Select 'Range("A2:A4000").Select 'Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1),Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True 'Cells.AutoFit Sheets.Add.Name = "Results" Sheets("Sheet1").Select 'change if needed Dim f As String Dim ba As Range Dim bb As Range Dim bc As Range Dim bd As Range Dim r As Range Set ba = Range("B2") Set bc = Sheets("Results").Range("A2") 'destination 1 Application.CutCopyMode = False ba.Copy bc.PasteSpecial xlPasteAll f = InputBox("Enter something to find.") Do While Not IsEmpty(ba) Set bb = ba.Offset(1, 0) Set bd = bc.Offset(1, 0) ba.Copy bc.PasteSpecial xlPasteAll On Error Resume Next Set sr = Range(ba.Offset(0, -1), ba.Offset(0, -1).End(xlToRight)) sr.Select If f < "" Then Set r = Nothing Set r = sr.Find(What:=f, After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End If If r Is Nothing Then Set bc = Nothing Else Application.CutCopyMode = False r.Offset(0, 1).Copy bc.Offset(0, 1).PasteSpecial xlPasteAll End If Set ba = bb Set bc = bd Loop End Sub test on your test data. works on xp2003 regards FSt1 "FSt1" wrote: hi, About half done. need to adjust based on your last post and add loop. hold on. working. how big is the import? FSt1 "JBS" wrote: Yes please, or in column A and B of the existing sheet if that is easier? "FSt1" wrote: hi, would be better. where do you want the results? next sheet? "JBS" wrote: Yes, it is a comma delimited file which imports into onecell per row in Excel. I can change it to be by doing "text to Data" 3010 123456789 4103 123.56 5101 123 3010 987654321 3011 abcde 4103 15000.00 5103 654 3010 654987321 4103 4523.69 5104 987 the result I am looking for would be 123456789 123.56 987654321 15000.00 654987321 4523.69 "FSt1" wrote: how is this data layed out? All in one cell? FSt1 "JBS" wrote: I have a file as follows 3010,123456789,4103,123.56,5101,123 3010,987654321,3011,abcde,4103,15000.00,5103,654 3010,654987321,4103,4523.69,5104,987 I need to search for the value 4103 and return the value after it eg. 123.56 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i referance an adjacent cell from a search? | Excel Discussion (Misc queries) | |||
| Return adjacent cell if conditional formatting exists. | Excel Worksheet Functions | |||
| Return value in adjacent cell | Excel Worksheet Functions | |||
| return value in adjacent cell different workbook | Excel Worksheet Functions | |||
| test a range of cells and return an adjacent value | Excel Worksheet Functions | |||
Linear Mode
