Split Data in a cell and get the value for the split data fr

I am not sure I have understood correctly.

do you have the data as in column A in sheet below.
I copied colA to col B and highlighted all the cells in column B and did text to columns with comma as delimiter. then I get column C and D

now see the formula in B11
after typing or copying the formula you should NOT just hit ENTER but hit conrol+shift+enter
then copy the formula down;.

the curly bracket {} in the formula shows that this is array fo;rmula and opoerated by control+shift+enter

is this what you want.

If not, I apologise for misunderstanding.

Hi there

If your data strings are always in the format 2 letters, 2 letters, 2 letters (ie total characters = 8) you can achieve this by formula. Assuming your data strings are in column A commencing A2 and your vlookup table in is E1:F3 (amend to suit) paste this in B2 and scroll down:

=AVERAGE(VLOOKUP(LEFT(A2,2),$E$1:$F$3,2,FALSE),VLOOKUP(MID(A2,4,2),$E$1:$F$3,2,FALSE),VLOOKUP(RIGHT(A2,2),$E$1:$F$3,2,FALSE))

If your data strings are of differing lengths then you will probably need to use code. Give some examples of different strings and the range of your vlookup table.

regards
Derek

Hi,

@Venkat,

No..dats not the way how I have the data...sorry for not providing enough info .
I do not have the data like how u have put.Somewhat similar though.
Let me explain properly:
1) In sheet 1, I have a table of individual data strings with its value

E.g
Data String Value
AA111 1
AB112 2
AC113 3
....etc.
The total number of rows maybe around 150...so I have about 150 different individual data strings. Width of each data string is 6 characters


2) In sheet 2, I have a table which looks like

Srl Data Stings seperated by ","
1 AA111,AB112
2 AB112,AC113,
3 A111, A112, A113
etc

The result required is:
- spliting of cell value into individual data strings
Eg: In the first cell...i want the split A111, A112 as A111 and A112
- Get the value of each of the data srting split from sheet 1 with the VLOOKUP or any other function or macro
- Average of the value of the data strings in that particular cells.
-lastly to repeat the same in the next row adn so on.

By what I learn, this has to be done by macro??It is true..?If yes, can anyone help me in acheiving the same


@Derek,

The data strings are always 6 charecters in length and not varying..is it possible to use the same formulae u have given..????

Thanks,
Deepak

sorry for misunderstanding , Just you have given samples of sheet 1 and sheet 2 it would have been helpful if you had given resulting range also.

sheet 1 --the text to column need NOT be done for individual cells but the whole first column consisting of

AA111 1
AB112 2
AC113 3

highlight the range of data in the column 1 and use text to column using delimiter as SPACE in the second step

the sheet 1 after this opertion is given below

sheet2- do the same operation using delimiter as comma.

sheet 2. is given below . here see formulas in columns E,F and G
now I am stumped . average of what? row no.2 in columns E and F etc.
I assumed this and I have done average in column H
now remember vlookup formula need NOT be done idnvidual cells.
if the formula is written in E2 you can copy the formula right and also down.
similarly column H once the formula is typed in H2 it can be copied down.

sheet 1 and sheet 2 finally will look like this
as the formula can be easily copied the spredsheet solution will be ok;. But due to some valid reason you prefer a macro it can be done.

you are equally welcome to contact me on my email if you do not get a better solution from an EXPERT.
VENKAT

Hi Deepak,

Your sheet1 data..

your sheet2 data...

Formula in B2 and copied down,

=SUMPRODUCT(SUMIF(Sheet1!$A$2:$A$4,BreakString,Sheet1!$B$2:$B$4))/COUNTA(BreakString)

where BreakString is a defined name.

to define the name

1. activate sheet2
2. hit ctrl+f3
3. type BreakString in names in workbook box
4. type =EVALUATE("{"""&SUBSTITUTE(Sheet2!$A2,",",""",""")&"""}") in refers to box
5. add

HTH

Hi Deepak,

Your sheet1 data..

your sheet2 data...


Formula in B2 and copied down,

=SUMPRODUCT(SUMIF(Sheet1!$A$2:$A$4,BreakString,Sheet1!$B$2:$B$4))/COUNTA(BreakString)

where BreakString is a defined name.

to define the name

1. activate sheet2
2. hit ctrl+f3
3. type BreakString in names in workbook box
4. type =EVALUATE("{"""&SUBSTITUTE(Sheet2!$A2,",",""",""")&"""}") in refers to box
5. add

HTH

Click to expand...

Hi Krishna,

your formulae worked like a charm....thanks a ton.I was trying too many things in macro..never knew the problem can be solved by use of formulas...you are a genius!!!

thanks again for all for ur time and effort...

Thanks,
Deepak