Hello everyone.
I have an excel file in which Column B contains 3 data (Insertion fee, Final value fee, Ad fee).
I need to split the values to separate columns.
I have attached my file for reference with desired output (Column F, G, H)
Thanks in advance.
I would use a simple IF() function (https://support.office.com/en-us/art...c-aa8bbff73be2 ). Something like =IF($C2=F$1,$D2,"none") in F2. Note the mix of relative and absolute references so you can simply copy this formula across and down.
Originally Posted by shg
Hi MrShorty,
With this formula I substituted none with value 0. What I needed is Ad fee column H has to be next to Column G.
Example: G3=3.5 and H3=0.42 (Now its in H4). It has to be in the same row. Not different rows.
Thank you
Try
F2Formula:
Please Login or Register to view this content.
Drag towards the cell
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
No Ankur not the desired answer.
I used a pivot table. Is this what you are looking for?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Naga i am unable to get u as per your required out put in F to H is giving through my formula... Let us know what do u want exactly
Hi Ankur,
Attached a file with desired output entered manually.
For some products there is Ad fee and for some its not. If there is Ad fee it has to be copied to the next adjacent cell.
Please check if you can help me out.
Thank you.
I Have created in your file L to Q for your desired output as G to K
Try
E2Formula:Please Login or Register to view this content.
M2CSEFormula:Please Login or Register to view this content.
L2Formula:Please Login or Register to view this content.
Q2Formula:Please Login or Register to view this content.
N2CSE and drag towards N2:P399Formula:Please Login or Register to view this content.
Check the attached file.
Hope this will help.
Wow Ankur. Amazing work. It simplifies my work a lot.
But here I made a mistake. For each data in Column D the Ad Fee comes first and then the Final value fee.
Previously I have mentioned as Final Value fee first and then the ad fee. Everything is correct only the Ad fee has to appear in the next row.
Thank you.
Naga Whatever you want please attach a sample with expected result.
It is not an easy to understand with messup data
Hi Ankur,
Check the attached file. Desired output is now changed.
Last edited by naga; 04-29-2017 at 07:15 AM.
Try to Change
O2Formula:Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
O2 or Q2 Ankur?
on O2 the drag it to Q and down....
Man Oh Man ! Thanks a lot Ankur.. Your help means a lot to me.
Great means you have got your solution, Glad to hear it.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ankur one more issue because of having Insertion fee column there are blanks in the Final Fee which affects the data. Can we remove it? I can add it separately
I am not getting you but remarkable above - Each formula work individual in column just don't remove helper columns. rest can be.
I removed the Insertion fee column Ankur. But the issue is I got blank cells in the column O.
Put the file and explain what you want....
File Attached. Columns O51:55 is blank needs to be removed. Same like for other rows as well. Blank cells has to be removed in the column O.
OMG you were asking in between formula blank cells no no no if you wish only non blank cell, I would suggest to make filter as if you will del the blank cells it would make impact on other cells and result can be wrong.
Each cells are connected with other.... whole column can be del only ...
Hope you understand. You can copy the result and paste it into an other workbook as value then you can remove them only.
Okay Ankur understood is this because of our data is being compared with the Helper column.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks