Link to home
Start Free TrialLog in
Avatar of Ackles
AcklesFlag for Switzerland

asked on

Excel fill data as per criteria

Hello Experts,
I have attached an Excel, which contains Data in the first column as raw data.
The data is starting with some identifiers as: Alphabet, Roman Number, Number, Small Alphabet & Article No.

I want to separate the data in different column as per the starting value of the data, I have made columns to demonstrate what exactly is the end result.

The last column is called Check, which actually should provide the result if the values in column A are contained somewhere from column C to column G.

Thanks in advance!
A
Sample.xlsx
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Try this.....

In A3
=IF(AND(ISNUMBER(MATCH(CODE(LEFT($A3,1)),{73,86,88,76},0)),LEFT(C$1,5)="Roman"),$A3,IF(AND(CODE(LEFT($A3,1))>=65,CODE(LEFT($A3,1))<=90,MID($A3,2,1)=".",C$1="Alphabet"),$A3,IF(AND(ISNUMBER(LEFT($A3,1)+0),C$1="Number"),$A3,IF(AND(CODE(LEFT($A3,1))>=97,CODE(LEFT($A3,1))<=122,LEFT(C$1,5)="Small"),$A3,IF(AND(LEFT($A3,3)="Art",C$1="Article No."),$A3,"")))))

Open in new window

And then copy across to column G and down the rows.

For details refer to the attached workbook.
Sample.xlsx
Avatar of [ fanpages ]
[ fanpages ]

^ Check cells [C4] & [D5].  There is a mismatch in the requirements.

The prefix "I." is being detected as being in the "Alphabet" column & the "Roman (Numeral)" column; as you would expect.

Should the "Roman" check be in advance of everything else &, hence, is any valid Roman Numeral symbol (I, V, X, L, C, D, &/or M) invalid within the "Alphabet" column?


Similarly, cell [D8] shows "II." as a valid "Roman" entry.

Hence, should any valid Roman Numeral (i.e. any numeric quantity in Roman Numeral form) be just in the "Roman" column?

That is, if the prefix to a value in column [A] contains a string of Roman Numeral symbols, but is not actually a valid Roman Numeral, does that mean that the column is "Alphabet" (only)?
Avatar of Ackles

ASKER

Looks Great!!!
Give me a day to test as I have to test.

Thanks, A
A: Please see my reservations within the comment immediately before your last comment (above).
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ackles

ASKER

sktneer,
Looks Great!

@fanpages,
Thanks!
Thanks. You need to test it to see if you get the desired output with all the possible input in col. A.
No problem, guys :)
Avatar of Ackles

ASKER

Gentlemen,
I have another stumble...
I have the export for the same stuff, but since data is from a text file it's spread across various columns...
Is there a formula to say, look from column B to column F & if you find something cut it & put in column A?

Thanks a lot in advance for your kind help!!!

A
Well that's completely a different question altogether.

Would be better if you mark this question as Solved by accepting one of the solutions provided as your original question has been already answered here and open another question with your new requirement along with a sample workbook. This way your new question will get more attention from the experts here.
Avatar of Ackles

ASKER

Agreed!!!
Will do now.
A
Avatar of Ackles

ASKER

Thanks a lot, it seems so far to be Excellent!!!
A
You're welcome. Glad to help.

As you can see that the experts have already responded to your new question. :)