Excel Formula help
Closed
itswin
-
Mar 4, 2010 at 03:46 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 4, 2010 at 05:30 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 4, 2010 at 05:30 AM
Related:
- Excel Formula help
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Transfer data from one excel worksheet to another automatically formula - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 4, 2010 at 05:30 AM
Mar 4, 2010 at 05:30 AM
you can do advance filter
suppose in sheet 1 the data is like this (row 1 colum headings-necessary)
person item
lady box
lady dice
lady box
man car
man car
man dice
child dice
child dice
child car
got ot sheet 2 and highlight A1
ciick data(menu bar)-filter -advance filter
in the advance filter window
slect radio button at the top "copy to another location"
list range is A1:
Sheet1!$A$1:$A$10
if you do not want to copy above click the small window at the end of the line "list range is" and take the mouse to sheet 1 column A a1 to A10 and hit enter key
crietira range leave it blank
copy to :$A$1
as you are in sheet 2 take the cursor to A1 and hit enter key
click the radio button at the bottom "unique record only)
shee 2 aform A1 will be
person
lady
man
child
regarding 2nd para
you can add in sheet 2 like this
a1 to D4
person box dice car
lady
man
child
the unique items like box, dice and car can also be enterered in B2 to D2 using advnace filter in columns away e.g. K1 and copy that list and paste special in B1 and clicking "translpose" in pastespecial window. You can take it as an exercise.
in B2copy paste this formula
=SUMPRODUCT((Sheet1!$A$1:$A$10=$A2)*(Sheet1!$B$1:$B$10=B$1))
(take care of dollar signs)
now copy B2 to the right to D2 and then upto row no 4
suppose in sheet 1 the data is like this (row 1 colum headings-necessary)
person item
lady box
lady dice
lady box
man car
man car
man dice
child dice
child dice
child car
got ot sheet 2 and highlight A1
ciick data(menu bar)-filter -advance filter
in the advance filter window
slect radio button at the top "copy to another location"
list range is A1:
Sheet1!$A$1:$A$10
if you do not want to copy above click the small window at the end of the line "list range is" and take the mouse to sheet 1 column A a1 to A10 and hit enter key
crietira range leave it blank
copy to :$A$1
as you are in sheet 2 take the cursor to A1 and hit enter key
click the radio button at the bottom "unique record only)
shee 2 aform A1 will be
person
lady
man
child
regarding 2nd para
you can add in sheet 2 like this
a1 to D4
person box dice car
lady
man
child
the unique items like box, dice and car can also be enterered in B2 to D2 using advnace filter in columns away e.g. K1 and copy that list and paste special in B1 and clicking "translpose" in pastespecial window. You can take it as an exercise.
in B2copy paste this formula
=SUMPRODUCT((Sheet1!$A$1:$A$10=$A2)*(Sheet1!$B$1:$B$10=B$1))
(take care of dollar signs)
now copy B2 to the right to D2 and then upto row no 4
