Friday, September 03, 2010   
  Search   
 

Office 2010 - Launch Event

Register  Login  
Forums  
     Minimize  

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.

     
  
 
Computer Forums for Windows UsersComputer Forums for Windows UsersMicrosoft Offic...Microsoft Offic...Microsoft ExcelMicrosoft ExcelArrange item of same type in the same rowArrange item of same type in the same row
Previous Previous
 
Next Next
New Post
 1/26/2010 11:11 AM
 

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

New Post
 1/26/2010 11:34 AM
 
 Modified By Ashish Mathur  on 1/26/2010 11:35:28 AM

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.


For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com

New Post
 1/26/2010 10:30 PM
 

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

New Post
 1/27/2010 6:41 AM
 

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.


For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com

New Post
 1/27/2010 2:01 PM
 

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

New Post
 1/27/2010 3:32 PM
 

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.


For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com

New Post
 1/29/2010 4:56 AM
 

Hi,

As requested, you may download the file from the following location. I have shared the formula here

http://www.mediafire.com/file/ngmnhwn... items by row.xlsx

Hope this helps.


For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com

New Post
 2/2/2010 12:06 PM
 

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

New Post
 2/2/2010 12:30 PM
 

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.


For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com

New Post
 2/2/2010 5:11 PM
 

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

New Post
 2/3/2010 5:45 AM
 

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.


For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com

New Post
 2/8/2010 3:02 PM
 

Thanks Ashish. It worked perfect. Now I will close this thread.

New Post
 2/8/2010 3:55 PM
 

You are welcome. Glad it worked and thank you for letting me know.


For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com

Previous Previous
 
Next Next
Computer Forums for Windows UsersComputer Forums for Windows UsersMicrosoft Offic...Microsoft Offic...Microsoft ExcelMicrosoft ExcelArrange item of same type in the same rowArrange item of same type in the same row



 

   User Log In Minimize  


Register
Forgot Password ?

     
  
   Top Contributors Minimize  
Most Active Users for 1 Week
UserTotalPosts
soumya 48
samagg 35
ankur mittal 27
vasu.jain 27
abhishek_ghosh_inc 22
     
  
   Follow Us Minimize  

MeraWindows

Follow abhishekkant on Twitter

Windows Live Alerts

     
  
     Minimize