LIVE GST Certification Course by CA Arun Chhajer begins 27th April. Register Now!!

Advanced excel

Kaushik Dedhiya (Associate CWA CA Final) (133 Points)

09 November 2015

Hi, 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.

Reply
Follow
Share

12 Replies

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.

Attached File : 62541 20151109210224 caclub 20151109 qry solved.xlsx downloaded: 280 times

1 Like

Ishav (Finance professional) (26 Points)
Replied 09 November 2015

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.

Attached File : 77982 20151109234107 366299 20151109182908 format ishav.xlsx downloaded: 195 times

1 Like

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.

Attached File : 366299 20151110102834 actual format.xlsx downloaded: 188 times

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 :)

Attached File : 62541 20151110115025 caclub 20151109 qry solved 2.xls downloaded: 238 times

1 Like

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,
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 :)

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,
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.

awesome. Thanks bro. Seems i need to learn lot from you.

rakesh varadkar (auditor) (29 Points)
Replied 11 November 2015

very useful

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.

Attached File : 192416 20151231221453 366299 20151110102834 actual format.xlsx downloaded: 151 times