|
MeraWindows Forums - Get your Computer Problems Fixed |
| This is a site run by the community - for the community. At the site, we share and contribute our computer learnings with each other. We discuss about Windows, Office, Mobile, Gaming and other computer related topics. At our forums you can post your queries and our very helpful community members will respond quickly to your queries. We organize lots of competitions from time to time and award prizes to the winners.
You will need to register at the site before posting in forums. It's absolutely free. After registering, you can get all the benefits available to our registered members, you can access our Downloads section, you can participate in contests, etc. You can post in forums in English as well as in Hindi, in fact we encourage you to use Hindi in your posts. If you have any problem with registration or login, please contact us.
|
|
 | | |
| | |
| | |
| | |
 | | |
|
|
|
 |
Joined: 1/10/2010
Posts: 8
|
|
|
Hello,
I have source data as mentioned below.
| Items |
|
|
| Type |
Occurrence |
| Item 1 |
Group 1 |
Group 11 |
| Item 2 |
Group 1 |
Group 12 |
| Item 3 |
Group 1 |
Group 13 |
| Item 4 |
Group 1 |
Group 14 |
| Item 1 |
Group 2 |
Group 21 |
| Item 1 |
Group 4 |
Group 41 |
| Item 2 |
Group 4 |
Group 42 |
| Item 1 |
Group 3 |
Group 31 |
Currently I am able to arrange the data like this:
| Occurrence |
Group 1 |
Group 2 |
Group 3 |
Group 4 |
| 1 |
Item 1 |
Item 1 |
Item 2 |
Item 1 |
| 2 |
Item 2 |
|
|
Item 1 |
| 3 |
Item 3 |
|
|
|
| 4 |
Item 4 |
|
|
|
Can you please help me in arranging the like items in the same row, i.e., like this. I must not resort the source data.
| Occurrence |
Group 1 |
Group 2 |
Group 3 |
Group 4 |
| 1 |
Item 1 |
Item 1 |
Item 1 |
Item 1 |
| 2 |
Item 2 |
|
|
Item 2 |
| 3 |
Item 3 |
|
|
|
| 4 |
Item 4 |
|
|
|
TIA to all.
Nagesh
|
|
|
|
| |
|
|
|
Hi,
Suppose your original data is in range B3 : D11 (headings are in row 3). In C14:F14, type Group 1, Group 2, Group 3, Group 4. In B15:B18, type 1,2,3,4. Now in cell C15, type the following formula which you may copy down and across
=IF(ISERROR(INDEX($B$3:$D$11,MATCH(C$14&$B15,$D$3:$D$11,0),1)),"",INDEX($B$3:$D$11,MATCH(C$14&$B15,$D$3:$D$11,0),1))
Hope this helps.
|
|
|
|
| |
|
|
|
Joined: 1/10/2010
Posts: 8
|
|
|
Ashish,
Thanks for the quick reply and too on a holiday. Your suggestion helped me partially. Let me explain again as mentioned below.
Essentially I want an item from column B to appear in either or both E & F from the same row like Item 1
In the example below, the headings are in row 2 & 3 and the data starts in row 4
| A |
B |
C |
D |
E |
F |
G |
| No. as mentioned by you |
Source Data |
(C4&COUNTIF($C$3:$C4,C4)) |
Data to be displayed |
|
| Occurrence |
Item |
Group |
occ - countif |
Group 1 |
Group 2 |
|
| 1 |
Item 1 |
Group 1 |
Group 11 |
Item 1 |
Item 1 |
B4 & B13 (Item1) are in Group 1 & 2 |
| 2 |
Item 2 |
Group 1 |
Group 12 |
Item 2 |
Item 10 |
`<- F5 must go to F14 |
| 3 |
Item 3 |
Group 1 |
Group 13 |
Item 3 |
Item 8 |
`<- F6 must go to F11 |
| 4 |
Item 4 |
Group 1 |
Group 14 |
Item 4 |
Item 11 |
`<- F7 must go to F16 |
| 5 |
Item 5 |
Group 1 |
Group 15 |
Item 5 |
Item 12 |
`<- F8 must go to F17 |
| 6 |
Item 6 |
Group 1 |
Group 16 |
Item 6 |
Item 13 |
`<- F9 must go to F18 |
| 7 |
Item 7 |
Group 1 |
Group 17 |
Item 7 |
Item 4 |
`<- F10 must go to F7 |
| 8 |
Item 8 |
Group 1 |
Group 18 |
Item 8 |
|
|
| 9 |
Item 9 |
Group 1 |
Group 19 |
Item 9 |
|
|
| 10 |
Item 1 |
Group 2 |
Group 21 |
|
|
|
| 11 |
Item 10 |
Group 2 |
Group 22 |
|
|
|
| 12 |
Item 8 |
Group 2 |
Group 23 |
|
|
|
| 13 |
Item 11 |
Group 2 |
Group 24 |
|
|
|
| 14 |
Item 12 |
Group 2 |
Group 25 |
|
|
|
| 15 |
Item 13 |
Group 2 |
Group 26 |
|
|
|
| 16 |
Item 4 |
Group 2 |
Group 27 |
|
|
|
Hope this explanation is better.
-nagesh
|
|
|
|
| |
|
|
|
Hi,
Try this array formula - kindly array enter the following formula (Ctrl+Shift+Enter)
=IF(E4="",IF(AND($C4=F$3,COUNTIF(F$3:F3,B4)>=1),"",B4),IF(ISERROR(IF(E4=INDEX($B$4:$C$19,MATCH(1,($B$4:$B$19=$E4)*($C$4:$C$19=F$3),0),1),E4,"")),"",IF(E4=INDEX($B$4:$C$19,MATCH(1,($B$4:$B$19=$E4)*($C$4:$C$19=F$3),0),1),E4,"")))
Please note that i did not need to use column D at all
Hope this helps. Do let me know how this works.
|
|
|
|
| |
|
|
|
Joined: 1/10/2010
Posts: 8
|
|
|
Ashish,
Can you please send the excel sheet you used to create this. I get "0' in mine. Unable to trace the error from my end.
thanks
nagesh
|
|
|
|
| |
|
|
|
Hi,
I do not have the file - i deleted it. Anyways, kindly ensure the following:
1. I hope you are confirming the formula with a Ctrl+Shift+Enter as against the conventional Enter;
2. In E4, enter=B4 and copy down
Kindly ensure the above two aspects and revert to me.
|
|
|
|
| |
|
| |
| |
|
|
|
Joined: 1/10/2010
Posts: 8
|
|
|
Ashish,
Sorry I could not get back soon as I had a death in family.
Well how about expanding this to the next level.
1. Change the group the item belongs to (example : Item 13 group 2 to Item 13 group 1)
2. Add new group and assign the item to new group and new column (example : item 5 group 1 to item 5 group 3) (add a new column group 3)
I am unable to think. It is going beyond my head (:
-Nagesh
|
|
|
|
| |
|
|
|
Hi,
My due condolences on the demise.
I am not at all clear about your question. Please be very specific/clear. Give range/cell references.
|
|
|
|
| |
|
|
|
Joined: 1/10/2010
Posts: 8
|
|
|
| A |
B |
C |
D |
E |
F |
G |
|
| |
Source Data |
Data to be displayed |
|
| Occurrence |
Item |
Group |
Group 1 |
Group 2 |
Group 3 |
| 1 |
Item 1 |
Group 1 |
Item 1 |
Item 1 |
|
B4 & B13 (Item1) are in Group 1 & 2 |
| 2 |
Item 2 |
Group 1 |
Item 2 |
Item 10 |
|
`<- F5 must go to F14 |
| 3 |
Item 3 |
Group 1 |
Item 3 |
Item 8 |
|
`<- F6 must go to F11 |
| 4 |
Item 4 |
Group 1 |
Item 4 |
Item 11 |
|
`<- F7 must go to F16 |
| 5 |
Item 5 |
Group 1 |
Item 5 |
Item 12 |
|
`<- F8 must go to F17 |
| 6 |
Item 6 |
Group 1 |
Item 6 |
Item 13 |
|
`<- F9 must go to F18 |
| 7 |
Item 7 |
Group 1 |
Item 7 |
Item 4 |
|
`<- F10 must go to F7 |
| 8 |
Item 8 |
Group 1 |
Item 8 |
|
|
|
| 9 |
Item 9 |
Group 1 |
Item 9 |
|
|
|
| 10 |
Item 1 |
Group 2 |
|
|
|
|
| 11 |
Item 10 |
Group 2 |
|
|
|
|
| 12 |
Item 8 |
Group 2 |
|
|
|
|
| 13 |
Item 11 |
Group 2 |
|
|
|
|
| 14 |
Item 12 |
Group 2 |
|
|
|
|
| 15 |
Item 13 |
Group 3 |
|
|
Item 13 |
Added a new Group - Group 3
Changed Item 13 from Group 2 to Group 3.
Hence remove it from Group 2 and move it to Group 3
|
| 16 |
Item 4 |
Group 2 |
|
|
|
|
Hope this explanations is better.
-Nagesh
|
|
|
|
| |
|
|
|
Hi,
While i can frame a formula based solution, i am sure your next question would be "What if i have more Groups". This approach will take care of as many Groups as you have
1. Select the data range of 2 columns (including the header row of Item and Group) i.e. B4:C19 and assign it a name [Ctrl+F3], say try
2. Select the data range of 2 columns (including the header row of Item and Group) i.e. B4:C19 and convert it to a Table/List (Ctrl+L)
3. Save the file on the desktop and click on any blank cell
4. Navigate to
a. Excel 2003 - Data > Import External Data > New Database Query > Excel files > OK
b. Excel 2007 - Data > Get External Data > From Other Sources > From Microsoft Query > Excel files > OK
5. Select the Excel file (saved in 3 above) and click on Next
6. Click on Next 2 times
7. Select "View or Edit Query in MS Query". Click on Finish
8. Click on SQL and type the following
TRANSFORM first(item)
SELECT item
FROM try
GROUP BY item
PIVOT group
9. Go to File > Return Data to MS Office Excel
10. In the Import box, select Table and the cell where you want the output. Click on Finish
11. You will get the desired output
12. Now when you edit any entry in the range try (as defined in step 1 above) or any new data by rows, just right click on any outout cell and Refresh the data
Hope this helps. Do let me know how this works.
|
|
|
|
| |
|
|
|
Joined: 1/10/2010
Posts: 8
|
|
|
Thanks Ashish. It worked perfect. Now I will close this thread.
|
|
|
|
| |
|
|
|
You are welcome. Glad it worked and thank you for letting me know.
|
|
|
|
| |
| | |
| | |
| | |
|
|
| |
|
|
User Log In
|
 |
|
| |
|
|
Top Contributors
|
|
|
Most Active Users for 1 Week
|
| |
|
|
Follow Us
|
|
|
|