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

Which formula is used without using the Pivot tables / filters

sgmpatnaik

sgmpatnaik

Active Member
Hello Sir,


I have one doubt, which formula is useful to shift the data without using the Pivot Tables and Filters, if we insert the some data in sheet1 how can we transfer the data in sheet2 base with the particular details (1) Month (2) Name


Sheet1 -

[pre]
Code: 
Sl. No.	Date	Month	Name	Product
1	02-04-2012	April	Rahul	Computer
2	03-04-2012	April	Rahul	Keyboard
3	04-04-2012	April	Shyam	Monitor
4	04-04-2012	April	Shyam	Keyboard
5	02-05-2012	May	Rahul	Monitor
6	03-05-2012	May	Shyam	Mouse
Sheet2 -

Report base with Month and Name

Month	April	Name	Rahul

Sl. No.	Date	Product
1	02-04-2012	Computer
2	03-04-2012	Keyboard
[/pre]
here is a sample file for better understanding


https://dl.dropbox.com/u/75654703/Sample.xlsx


NOTE: - replay is not necessary if you think this question is helpful to some one then please
 
SP


Have a look at the technique Luke M used here: http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
Thank Q Hui Sir


i am little bit confuse how can the formula use for Month and Name


if possible please give a sample


Thanking You


Once again for your replay
 
Hi sgmpatnaik,


In your spreadsheet:


In sheet2 do the following:


1)at C2(right above the month "April" which you have highlighted as yellow) write the following formula which will give you the value of month written in text:

=MONTH(DATEVALUE("01-"&C3))...will give you 4 at C2 for "April" at C3


2)AT E2 give the reference of E3 to fetch the name separately(I mean at E2 write =E3)

[Note: I did it deliberately as I do not want to touch the name you placed at E3]


In sheet1 do the following:


1)Insert 2 columns at the begining


2)Now at column B, at B4 write =MONTH(D4)...and drag it down..this will give you the value of month of the corresponding date at D col

[Note: So now we have the 'number of month' for date and Month written in text]


3) At A4 write =B4&F4...and drag it down...(to concatenate your look values)


Now at sheet2, at B7 =VLOOKUP($C$2&$E$2,Sheet1!$A$4:$G$9,4,0)....to fetch date [ custom format the cell as m/d/yyyy)

at C7 =VLOOKUP($C$2&$E$2,Sheet1!$A$4:$G$9,7,0)...to fetch product [custom format the cell as m/d/yyyy)]


Last part can also be done using SUMPRODUCT instead of VLOOKUP, in that case you would not need to create a lookup key as I did in sheet1 by cancatenating the look values of interest.


Hope this helps..


Please let us know if we are able to help you..


Regards,


Kaushik
 
Thank Q Mr. Kaushik for your help


It's work little bit but not complete


If we copy and Drag Down the Formula in sheet2 then it's display only the 2/4/12 and Computer


Thanks Once Again
 
Hi sgmpatnaik,


My apologies.....vlookup will not work as there are multiple records for same field..I did not notice this..


We have to use the following array formula to tackle this.


At sheet2, at b7 =IFERROR(INDEX(Sheet1!$D$4:$D$9,SMALL(IF(Sheet2!$C$2&Sheet2!$E$2=Sheet1!$A$4:$A$9,ROW(Sheet1!$A$4:$A$9)-MIN(ROW(Sheet1!$A$4:$A$9))+1,""),ROW(A1))),"")...press (CTRL+SHIFT+ENTER)to enter as an array formula (not only enter)....drag down till whatever the rows you want.

at C7: =IFERROR(INDEX(Sheet1!$G$4:$G$9,SMALL(IF(Sheet2!$C$2&Sheet2!$E$2=Sheet1!$A$4:$A$9,ROW(Sheet1!$A$4:$A$9)-MIN(ROW(Sheet1!$A$4:$A$9))+1,""),ROW(A1))),"")....and do the same.


Hope this works.


Let me know if you face any challenge though...


Regards,

Kaushik
 
Hi Kaushik


It's Working Great,


Small doubt is it not work with out create the No&Text format in Sheet1 and Sheet2
 
sgmpatnaik,


Glad that it works fine for you now..


As of now I can not think of doing this without having a concatenated lookup value (as you have two criteria...date and name) in place.


However, considering the tremendous flexibility nature of excel, one thing can surely be done in multiple ways which, unfortunately, I can not think of doing at this point of time.Probably(I am sure), some of our excel Ninjas can come up with an alternative approach....


Regards,

Kaushik
 
Thank Q Kaushik G


Thanks Once Again for Suggestion which is your spend some time for my Question and very glad to meet with you


Have a Nice


With Regards


Patnaik
 
You must log in or register to reply here.
Back
Top