I will be greatful for help. I will fill cell F1 marked with blue. I want Colum F to M marked with yellow. Toughest part column A are in text. In India it is late night i am sure In morning I will get favor from froum.
I will be greatful for help. I will fill cell F1 marked with blue. I want Colum F to M marked with yellow. Toughest part column A are in text. In India it is late night i am sure In morning I will get favor from froum.
Hi jpbisani, try this:
Formula:Please Login or Register to view this content.
Don't forget to use the Ctrl+Shift+Enter to do the "{ }" on the formula
John.
"I excel at jumping to conclusions"
I tried I have not got the answer. In column G,i,k & m i have written formula of index & match (=INDEX($B$1:$B$16,MATCH(F2,$A$1:$A$16,0))) and got correct answer but not able to get correct formula in row column F2:f5, H2:h5, I2:i5 & l2:l5. f1 have 5 character. we have to take 1st four character add a wildcard and pick all that match with first four character of F1. Hope now i have explained my problem correctly.
in cell f2 this formula to be written with a small change. formula is "IFERROR(INDEX($A$2:$A$16,AGGREGATE(15,6,(ROW($A$2:$A$16)-ROW($A$1))/($A$2:$A$16=($F$1)),ROWS($A$1:A1))),"")" change required instead of "$f$1" it should be "Left($F$1,len($f$1-1))&"*". but when I change it dose not work. Could not trace the error when using wild card. requires forum assistance.
The following formulas replicate the values that were found in the file attached to post #1:
1. In F2 and down: =(MID(F1,1,(LEN(F1)-1)))&ROWS(A$1:A1)
2. In H2 and down: =IF(ISNUMBER(MATCH(TEXT(SUM(NUMBERVALUE(H1),1),"0"),$A$1:$A$16,0)),TEXT(SUM(NUMBERVALUE(H1),1),"0"),"")
Note: Copy cell H2 and Paste in J2 and L2.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Very great thank you JeteMc. Highly obliged. I just last hope but you gave me the way & answer. Whereas in F1 last digit was 1 and if it was greater it omitted 1. I have made correction in 2nd row of your formula. attach worksheet
You have showed the light and i have crossed the bridge. Again thank you a lot.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks