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

Sort values of a list (s) to groups by matching the values in 4 existing groups

Z

Zep_

New Member
Here are the demo values to illustrate the question

[pre]
Code: 
DATA1	DATA2	GR1	GR2	GR3

0002	0002	0002	0002	0015
0011	0011	0037	0011	0037
0015	0015	0038	0030	0038
0040	0040	0040	0043	0040
0041	0041	0041	0632	0041
0042	0050	0042	0634	0042
0206	0206	0043	0638	0065
0207	0300	0044	0685	0206
0632	0632	0206	0688	0207
0634	0634	0207	1135	0685
0638	0638	0620	1140	0688
0685	0666	0632	1180	1109
0688	0688	0634	1184	1210
1102	1102	0638	1191	1217
1109	0999	0685	1192	1230
1114	1114	0688	1193	1275
1135		1135	1212	1279
1141		1141	1246	1302
1180		1212		1303
1192		1242		1308
1217		1277		1341
1230
I would like each value from DATA Columns to be matched to values in GR1, GR2, GR3. What ever is not matched, should go into column Remaining. For example, the output for DATA1 one Should be like:

GR1	GR2	GR3	REM
0002	0015	1114	1102
0011	0040	1141
0632	0041
0634	0042
0638	0206
0685	0207
0688	0685
1135	0688
1180	1109
1192	1217
1230
[/pre]
It is to be noted that there are multiple DATA lists and it would be great if it is possible to dynamically choose different DATA lists for corresponding G and Rem columns. There can be multiple matches for some values (see 0685, 0688)


I have seen some amazing replies on this forum. I would be highly obliged if somebody could propose solution to this, it would save my 'face'! I hope I was clear enough though. Thank you


PS:Thanks for editing help mods
 
Hi ,


Shouldn't the result be as follows :

[pre]
Code: 
GR1	GR2	GR3	REM
0002	0011	0015	1102
0040	1180	1109	1114
0041	1192	1217	0050
0042		1230	0300
0206			0666
0207			0999
0632
0634
0638
0685
0688
0632
1135
1141
[/pre]
I have not put down the values which occur in the GR1 , GR2 and GR3 columns which do not appear in the DATA1 and DATA2 columns.


Narayan
 
Hello,


Thanks for reply. The result has to be different for every data list; it cant be combined with other DATA lists. The result i presented is for column DATA1 ( > For example, the output for DATA1 one Should be like)


I believe the solution has to be with Pivot table or VLOOKUP or a combination of both?


PS: Please ignore header displacement of result demo in my question.
 
Hi ,


That's fine ; in that case , the output would still be as follows , right ?

[pre]
Code: 
GR1	GR2	GR3	REM
0002	0011	0015	1102
0040	1180	1109	1114
0041	1192	1217
0042		1230
0206
0207
0632
0634
0638
0685
0688
0632
1135
1141
[/pre]
Values which occur in GR1 , GR2 and GR3 but which do not occur in DATA1 have not been shown.


Narayan
 
I mixed up badly. Your first reply is right yes (And I think you already have a reply :D)


Actually the data in GR3 is wrong and my 'demo' output is according to 'right' data. But I think you have sorted them right in your first reply. Except the values from data lists should not be combined to which you seem to agree. Lastly, the values can be repeated in different GRs.


For example, in your first post, 0002 should be present in GR2 and GR3


Thank You Narayan
 
Hi ,


OK. So we are agreed that values which occur in more than one of the columns GR1 , GR2 and GR3 should be repeated in them.


The second point is what about the values which occur in GR1 , GR2 or GR3 , which do not occur in DATA1 ? Should they be present in the output in their respective columns ?


I am giving below the output as I see it ; please confirm.



GR1 --- GR2 --- GR3 --- REM



[pre]
Code: 
0002	0002	0015	1102
0040	0011	0040	1114
0041	0632	0041
0042	0634	0042
0206	0638	0206
0207	0685	0207
0632	0688	0685
0634	1135	0688
0638	1180	1109
0685	1192	1217
0688		1230
0632
1135
1141			

0037	0030	0037
0038	0043	0038
0043	1140	0065
0044	1184	1210
0620	1191	1275
1212	1193	1279
1242	1212	1302
1277	1246	1303
1308
1341
[/pre]
Narayan
 
Values which occur in GR1 , GR2 or GR3 but not occurring in DATA1 should not be displayed :)


EDIT: So yeah, second part of the output you provide is not needed
 
The values are well distributed. There is a little problem, when I change a value in DATA1, it goes straight to REM, no matter it is present in GRs list or not.


I feel like a jerk being so dependent, I will do some manipulation myself and will get back to you with whatever outcome it is. Big thanks for participation!
 
Hi ,


Sorry , my mistake.


It's late at night now , and I can rectify my mistake only tomorrow morning.


I hope you can wait.


Narayan
 
Hi ,


I think it has to do with the cell formats ; I had initially made all of the cell formats TEXT ; in case some of the cells are in NUMERIC format , this can create a problem.


Can you make all of the cell formats NUMERIC and check if the problem still persists ?


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