Kaushik Dedhiya (Associate CWA CA Final) (133 Points)
09 November 2015Hi, i wanted to know whether we can bring Item Batch in Parellal Line to Item Name as shown in Excel Attached Format. 'Present' Sheet contains the default report derived from system. 'Required' Sheet contains the desired result required.
I know it seems illogical but some how using advanced excel functions as search, isnumber, if, text, we can crack it out.
I am not too sure of success in this just giving it a try for some ultra knowledged excel expert. Thank YOu in advance.
Mehul Thakkar
(CA CISA DISA CEH MOS Excel Expert)
(194 Points)
Replied 09 November 2015
Hi,
There are many ways to do this. I found a quick solution that works for the example file you gave here.
Hardly 3-4 steps and you are done.
Ishav
(Finance professional)
(26 Points)
Replied 09 November 2015
Hi
Very simple. Just type this formula in cell A2 : =LEFT(B2,FIND(" ",B2&" ")-1) and drag it till end. Done! As mentioned by Mehul, there are lot of ways to do it.
Kaushik Dedhiya
(Associate CWA CA Final)
(133 Points)
Replied 10 November 2015
Hi, Thanks mehul and Ishav. I think i have given too simple format. Let me share the actual data only so that you can understand the question better. The formula you shared is not possible to apply for my issue. You will be able to understand why once you will see the file.
I believe and hope you will be able to crack it down. Thank You Guys in advance. Even if you can't together we can scratch our brains and crack it down.
Mehul Thakkar
(CA CISA DISA CEH MOS Excel Expert)
(194 Points)
Replied 10 November 2015
Hi,
Just saw your reply, here's is my other solution its a macro.
Select your data click on the button i have create, and it will print the data in next 2 columns.
then just delete some unwanted rows which have "delete by sorting" written in them.
Macro Magic always works :)
Kaushik Dedhiya
(Associate CWA CA Final)
(133 Points)
Replied 10 November 2015
only one thing i can say is wooooooooowwwwwww!!! awesome mate. Frankly i have zero knowledge of macro. I Guess you used power of visual basic.
Can you explain what logic you used?? Thanks a TON bro. It will help tremendously.
Kaushik Dedhiya
(Associate CWA CA Final)
(133 Points)
Replied 10 November 2015
| Originally posted by : Mehul Thakkar | ||
 |
Hi, You definitely are an excel expert i must say. Kudos to you. |
 |
Mehul Thakkar
(CA CISA DISA CEH MOS Excel Expert)
(194 Points)
Replied 10 November 2015
Hi,
Am glad it worked. Yes I have used macros/VBA.
You can goto Developer tab > Macros > You will see something called itemnameloop, so just click on it and select edit. That way you can see the code.
The logic I used is as follows:
I observed your item names have 5 digits generally towards end, so if digits are found at end I copy that to right side, if digits not found then its presumed item grp and keep repeating it till new item grp/head is found.
Kaushik Dedhiya
(Associate CWA CA Final)
(133 Points)
Replied 10 November 2015
| Originally posted by : Mehul Thakkar | ||
|
Hi,
awesome. Thanks bro. Seems i need to learn lot from you. |
|
Manikandan
(Senior Accountant)
(184 Points)
Replied 25 December 2015
Mr.Kaushik, I think may I know A simple ways(without macros) in this above subj., I explain step by step..
>Select all and press ctrl + alt + L (already filter option is applied in your sheet)
>select filter by colour(in filter option) _ eg. select Red colour (head)
>Insert 1 entire column
>select the head and press ctrl + R (copy to new column)
> Remove your filter and select all > copy and past in value format then enjoy
T.S.Vignesh
(Audit Manager)
(76 Points)
Replied 31 December 2015
I have nothing against macros personally. But I find using macros for such a simple problem seems like a overkill. I have attached the solved file here for everybody's reference. This does not even require any formulas other than an equal formula. Hint I use the Goto dialog box for solving this
Anyone interested in my method of doing this, please feel free to ask.
I am attaching the partially solved file. Anyone can make out the rest.
Mehul Thakkar
(CA CISA DISA CEH MOS Excel Expert)
(194 Points)
Replied 01 January 2016
Good to see so many solutions.
Since excel is an open tool any method that gives the correct end result is fine I guess.
Lesser the no. of steps the better it is.
I would have even suggested something like Power Query, but its awareness is a problem.
Excel Mastery Program
CAclubindia
