• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Remove the Blanks ????

T

Tom90

Member
Hi All, I have been trying all sorts of Formula to achieve the out come that I require but I cannot get one so I am asking for help if anyone can provide a formula or VBA that will achieve this for me, I have attached a excel file with the data and formulas that I am using,

I have a VBA that Text to Columns for me so that it splits the data into each cell then I have a formula to look for anything starting with C6 and that then goes in its own cell but what I am looking for is to have all my data to run down in column "AA" and were there is more than two C6 in the same row is to have it put in the next column "AB" and "AC" the way I have shown it in the file,hope I have explained. Thanks Tom90
 

Attachments

  • Test 5.xlsx
    12 KB · Views: 5
Hi Narayan, that looks just what I need but there is one problem but I think that is my skill and knowledge of excel, when I move it to the working file when I copy it over the formula display as text also is the test file my main columns where "A to F" but on my working file it is "L to Q" as I had to make the file as small as possible to load it onto the post, also what is the meaning of the { in front of the = and at the end of the formula, but my main problem is that the formula is displaying a text as per below
{=IFERROR(MID(INDEX($L2:$Q2,SMALL(IF(LEFT($L2:$Q2,3) = "JIN", COLUMN($L2:$Q2)), COLUMNS($L:L))), 5, 255), "")}
 
Hi ,

To take care of the situation in your working file , use the following formula :

=IFERROR(MID(INDEX($L2:$Q2,SMALL(IF(LEFT($L2:$Q2,3) = "JIN", COLUMN($L2:$Q2) - MIN(COLUMN($L2:$Q2)) + 1), COLUMNS($L:L))), 5, 255), "")

This is what is known as an array formula ; what this means is that you type this formula in a cell and press the ENTER key , it will not work correctly.

For array formulas to work correctly , you have to press the following 3 keys together :

CTRL SHIFT ENTER

When you do this , Excel automatically inserts the parenthesis around the formula , signifying that it is an array formula. Manually typing in these parentheses will not work.

For more details on array formulas , check out this link :

http://www.excel-easy.com/functions/array-formulas.html

This has 11 pages in it , covering nearly a dozen examples of array formulas.

Narayan
 
Hi Narayan,
This has work great thanks for you help and given me the extra knowledge in Excel, Thanks again
 
You must log in or register to reply here.
Back
Top