Data extracting and Arrangement

[sampath]

Hello Friend,


I need to extract the particular data from grouped data's and which extracting data need to arrange another sheet.


Here i mentioned some data's what i want.....


Input (Sheet 1)

[pre]

-------    A                      B                        C                          D
1      RT5005 - R      RT5005 - R - RM-BVRS      RT5005 - R - RM-ST-MAN     RT5005 - R - ST
Output (Sheet 2). 


Condition for output.

1.    extract the data after "RT5005 - R -".

2.    that data will placed at sheet 2 and  "C" column.


Example output from sheet 1 Input

---------   A              B             C
1
2                                     RM-BVRS
3                                     RM-ST-MAN
4                                     ST

[/pre]
Please help me to get the data using macros as i mentioned above.


Thank with regards,

Sampath.S

 

[Faseeh]

Hi Sampath,


Try this with your data in Col A:

=TRIM(MID(A1,FIND("Your Criteria",A1)+5,99))

...Adjust +5 so that you get the correct result.


Regards,

 

[sampath]

Dear Faseeh,


Thanks for your reply....


I couldn't understand your answer. I need to extract RT(need at B1 Cell) from RT5005 - R - ST ( which was placed at A1 cell) .


Thanks for better understanding.


Regards,

Sampath.S

 

[Faseeh]

Hi sampath,


If the cell A1 contains

RT5005 - R - RM-BVRS you can try following formula:


[code]=TRIM(MID(A1,FIND("- R -",A1)+5,99))

..this will give you following as result:


RM-BVRS[/code]


Hope that helps!


Regards,

 

[sampath]

Dear Faseeh,


It working fine. Thanks for your assistance.


I need onemore help. How can i get this output in sheet 2 (C1 cell, While Input is available in sheet 1 (A1 cell).


Thank with regards,

Sampath.S

 

[Faseeh]

Dear Sampath,


Enter this formula where you want it to be (say in sheet 2) and replace A1 in formula with Sheet1!A1 or use your pointing device (mouse) to manually replace the reference.


Regards,

 

[sampath]

Dear Faseeh,


I have another one point to this extracting...


Some time that criteria(RT5005 - R - RM-BVRS)comes in A1 cell and some time it comes in A4 or A6 cell.


So, How to find that Criteria automatically with your already given function.


Can i use =TRIM(MID(A:A,FIND("RT5005 - R -",A:A)+5,99)).


And onemore thing, When i got data after applying this function. I can't Copy that result to that particular cell. bcoz if i copy that output cell, that function only copied. pls how to copy the output data from that particular cell.


Please suggest me for the same.


Regards,

SAMPATH

 

[Faseeh]

Hi sampath,


I assumed that your data in present in col A so you can simply drag down the formula down the column and check the results.


For copying the results/output, copy and paste special>values only so that will not copy formula but only results.


Regards,

 

[sampath]

Hi Faseeh,


In pervious converstion, I need the Criteria finding in your given function.


Sometime, That Criteria(RT5005 - R - RM-BVRS) comes at Cell A1 and sometimes which will comes in A4 or A5 cell.


Can you help me to find that particular criteria using your given function.


This is your given function "=TRIM(MID(A:A,FIND("RT5005 - R -",A:A)+5,99))"


Once again thanks for your assistance.


Regards,

Sampath.S

 

[Faseeh]

sampath,


Now i am unable to understand your exact requirement, refrering to your last post, since your criteria might not be present in A1, you want result like this:

[pre]

Row 1	RT5005 - R - RM-BVR1	RM-BVR1
Row 2	Blank	                RM-BVR2
Row 3	Blank	                RM-BVR3
Row 4	RT5005 - R - RM-BVR2	Blank
Row 5	Blank	                Blank
Row 6	Blank	                Blank
Row 7	Blank	                Blank
Row 8	Blank	                Blank
Row 9	RT5005 - R - RM-BVR3	Blank

[/pre]
Please confirm.


Regards,

 

[sampath]

Dear Faseeh,


My input (Sheet1).

Column A Column B Column C Column D

Row 1 RT5005 - R - RM-BVR1(this is in column A1)

Row 2

Row 3 RT5005 - R - RM-BVR2(This is in Column C3)

Row 4

Row 5 RT5005 - R - RM-BVR3(this is in Column D5)

Row 6


Needed Output (Sheet2).


Column A

Row 1 RT5005 - R - RM-BVR1

Row 2 Blank

Row 3 RT5005 - R - RM-BVR2

Row 4 Blank

Row 5 Blank

Row 6 RT5005 - R - RM-BVR3


I hope this is is understandable. please help me to get that output in sheet 2.


please how can i send the .xls softcopy to you. if it is not understandable


Regards,

Sampath.S

 

[Faseeh]

Hi Sampath


Please see this file, hopefully it works:


https://dl.dropboxusercontent.com/u/60644346/sampath_consolidate.xlsx


Regards,