• 100% Satisfaction Guarantee
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1959
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

# I have 2010 and need formulas to combine the info based

### Customer Question

Hi. I have 2010 and need formulas to combine the info based on a source being in the first column. The first column is gong to be a list of unique assignment numbers that are organized based by age of the assignment/oldest on top and newest on bottom. The second column reflects the age of each assignment, oldest on top the third column are unique people's names of who to give the assignments to, the fourth column reflects the number of assignments to give each of the people from column three. The trick is, most of the people from column three will be receiving a different number of assignments, and I need to make the assignment rotations based on oldest first down to the newest, with each rotation eliminating the people who should receive less and not part of that rotation. I am looking for a formula, pivot or macro to do this, because I could have as many as 800 unique assignment numbers ranging from 100 days old down to a day old and upwards of 40 people to receive as many as 12 assignments, down to two, all based on aging assigned first. Thank you
Submitted: 1 year ago.
Category: Microsoft Office
Customer: replied 1 year ago.
Please let me know if you need any more details, thanks
Expert:  Jess M. replied 1 year ago.
Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.Can you please send me a dummy or sample of your Excel file so that I can directly check your required formula? You can attach it here when you reply, or you can upload it to http://filesXpress.com and then give me the short download link.Thank you.Best regards,Jess
Customer: replied 1 year ago.
I don't have a sample to provide you, just a hypothetical description. Fist column will be 120 unique combination of numbers, which represent assignment numbers, second col is the age of those numbers/assignments(both 1st and 2nd columns are sorted by aged with oldest numbers on top in col 1, highest number of days old on top if col 2, which represents days old. The third col will be the people who will receive assignments. The fourth col will be how many assignments by age, each person in col 3 will receive, I may assign some people the same amount and others less, adding to the 120 total assignment numbers in the first col. Col 1 is titled assign number, col 2 is titled days aged, col 3 is name, com 4 is number to name. Really the fourth column is not needed, if a macro is made to assignor the numbers in order of age, and how many to which person...all people being assigned by age, so everyone gets equal aging, etc. thanks
Expert:  Jess M. replied 1 year ago.
And are you trying to automate the assigning of assignments in column 4?
Customer: replied 1 year ago.
Hi hi yes I am trying to automate the assignments to each person's name according by aging and according to the number they are should receive, the columns can be moved around or changed if that helps you come to a solution. Thanks
Customer: replied 1 year ago.
Smaller portion simple Example: if I have only five people receiving assignments and there are only 25 assignments(individual numbers) that are arranged by aging, then I need the formula to have all five people receive five cycles going down the list, not one name five times at the top then the name five times down. But names 1-5 at top, then names 1-5 Continue to go down. ( in my situation, the cycles going down would change bases upon the number of people left reviving assignments in the different cycles as they go down the assignment number list, thanks
Expert:  Jess M. replied 1 year ago.
Hi,
I am sorry but I will not be able to help you with this. I will opt out so that my fellow experts can assist you further.
You will be notified through email when someone joins you here.
Thank you.
Regards,
Jess
Customer: replied 1 year ago.
No content came through on your last email, pls try again, thanks
Expert:  The-PC-Guy replied 1 year ago.
hi, I would like to be able to help with this, but it is rather difficult without a visual example of what you are trying to achieve. I assume in wouldn't be too difficult to put together an example, 1 sheet having example data, and a second sheet showing what you would like the output to look like. This would be very helpful in determining what is needed.
Customer: replied 1 year ago.
I have already received help from another person, but cannot access his recent response
Expert:  The-PC-Guy replied 1 year ago.
sorry, he was unable to assist you further so he sent the question to me.
Customer: replied 1 year ago.
Did this come through to you from the past helper? :::I don't have a sample to provide you, just a hypothetical description. Fist column will be 120 unique combination of numbers, which represent assignment numbers, second col is the age of those numbers/assignments(both 1st and 2nd columns are sorted by aged with oldest numbers on top in col 1, highest number of days old on top if col 2, which represents days old. The third col will be the people who will receive assignments. The fourth col will be how many assignments by age, each person in col 3 will receive, I may assign some people the same amount and others less, adding to the 120 total assignment numbers in the first col. Col 1 is titled assign number, col 2 is titled days aged, col 3 is name, com 4 is number to name. Really the fourth column is not needed, if a macro is made to assignor the numbers in order of age, and how many to which person...all people being assigned by age, so everyone gets equal aging, etc. thanks
Customer: replied 1 year ago.
If you can work with what I told you, let me see if I can make a dummy
Expert:  The-PC-Guy replied 1 year ago.
yes, I did see the previous conversation, however what I am asking is for just a simple example of the data, and how you want the output to appear
Customer: replied 1 year ago.
How much longer will you be online? I can do it within an hour
Customer: replied 1 year ago.
I have to go to a PC, not my phone
Expert:  The-PC-Guy replied 1 year ago.
i'm on and off, if you send a reply here, I should see it
Customer: replied 1 year ago.
Assignment number AGE AssigneeName NumberToAssignee
H_G_CM20120529P21017 76 John 4
2014D0630NG92464140 76 Tom 7
201N40731NG284572627 76 Bob 11
20E140731NG284574919 76 Lynn 8
201407H31NG284575575 76 Jane 8
(###) ###-####1NG284586301 76 Joe 9
20X140831NG94578289 76 Mary 3
20140930UNG94751954 76
20Z140930NG94757894 76
201A40930NG94764318 76
20141024NAMGCG00111 74
20141031NG95P022886 74
201W41031NG95023798 74
20141L031NG95025266 74
2E0141031NG95036186 73
20O141105NAMGCG00116 73
20F141130NG95284853 73
20B141217NAMGCG00003 73
20141P218NAMGCG00190 73
201N41231NG95796838 73
20141T231NG95811498 73
20A150126NAMGCG00099 73
201F50127NAMGCG00201 73
20Y150131NG96816320 71
20R150131NG96828440 71
2015R0205NAMGCG00145 71
2015J0205NAMGCG00152 71
20B150226NAMGCG00178 71
20150228NKG97160972 71
20C140630NG92466842 70
20K140630NG92468940 70
201I40630NG92471998 70
20Y140725NAMGCG00107 70
20P140731NG284584885 69
20140731NG2J84589473 69
201407L31NG284595719 69
201H40819NAMGCG00006 69
2014S0831NG94564925 69
20R140831NG94594251 69
2S0140831NG94606915 69
20140929NAMGICG00097 69
2014J0930NG94729600 69
20M140930NG94742558 68
20Q140930NG94760992 68
2P0140930NG94761298 68
2A0141010NG2030 67
20141020NAAMGCG00097 66
20141031NYG95032646 65
20141F110NAMGCG00075 65
2B0141110NAMGCG00081 64
Customer: replied 1 year ago.
The four columns are assignment, age, assignee and number to assignee. My next email will be my end result if the cycles
Expert:  The-PC-Guy replied 1 year ago.
anyway you can send the actual excel file?
Customer: replied 1 year ago.
Assignment number
AGE
Assignee
H_G_CM20120529P21017
76
John
2014D0630NG92464140
76
Tom
201N40731NG284572627
76
Bob
20E140731NG284574919
76
Lynn
201407H31NG284575575
76
Jane(###) ###-####1NG284586301
76
Joe
20X140831NG94578289
76
Mary
20140930UNG94751954
76
John
20Z140930NG94757894
76
Tom
201A40930NG94764318
76
Bob
20141024NAMGCG00111
74
Lynn
20141031NG95P022886
74
Jane
201W41031NG95023798
74
Joe
20141L031NG95025266
74
Mary
2E0141031NG95036186
73
John
20O141105NAMGCG00116
73
Tom
20F141130NG95284853
73
Bob
20B141217NAMGCG00003
73
Lynn
20141P218NAMGCG00190
73
Jane
201N41231NG95796838
73
Joe
20141T231NG95811498
73
Mary
20A150126NAMGCG00099
73
John
201F50127NAMGCG00201
73
Tom
20Y150131NG96816320
71
Bob
20R150131NG96828440
71
Lynn
2015R0205NAMGCG00145
71
Jane
2015J0205NAMGCG00152
71
Joe
20B150226NAMGCG00178
71
Tom
20150228NKG97160972
71
Bob
20C140630NG92466842
70
Lynn
20K140630NG92468940
70
Jane
201I40630NG92471998
70
Joe
20Y140725NAMGCG00107
70
Tom
20P140731NG284584885
69
Bob
20140731NG2J84589473
69
Lynn
201407L31NG284595719
69
Jane
201H40819NAMGCG00006
69
Joe
2014S0831NG94564925
69
Tom
20R140831NG94594251
69
Bob
2S0140831NG94606915
69
Lynn
20140929NAMGICG00097
69
Jane
2014J0930NG94729600
69
Joe
20M140930NG94742558
68
Bob
20Q140930NG94760992
68
Lynn
2P0140930NG94761298
68
Jane
2A0141010NG2030
67
Joe
20141020NAAMGCG00097
66
Bob
20141031NYG95032646
65
Joe
20141F110NAMGCG00075
65
Bob
2B0141110NAMGCG00081
Expert:  The-PC-Guy replied 1 year ago.
that is coming through on my end as all text, and I can't make sens of it. Can you send the actual file to www.wikisend.com after uploading they give you an ID#paste the ID# *****
Customer: replied 1 year ago.
Having a hard time copyng the whole file, I could forward it to you in email. What email should I send to? Thanks
Expert:  The-PC-Guy replied 1 year ago.
you can send it to***@******.***, and reference this question in the subject line. NOTE: It may take them some time to get it to me this way
Customer: replied 1 year ago.
I emailed it to my phone and only have my phone now, so all I can do is forward to an email. I don't have the file downloaded, in order to use wiki. If you give me an email, I'll forward thanks
Expert:  The-PC-Guy replied 1 year ago.
you can send it to***@******.***, and reference this question in the subject line. NOTE: It may take them some time to get it to me this way
Customer: replied 1 year ago.
Sent from my iPhone
Customer: replied 1 year ago.
Let me know if file was attached, thanks
Expert:  The-PC-Guy replied 1 year ago.
nope
Customer: replied 1 year ago.
I added screen shots of data and results, assigned by age according to how many each person was to be assigned. Thanks
Expert:  The-PC-Guy replied 1 year ago.
ill wait for the file, thanks
Customer: replied 1 year ago.
It would really only take you 5 mins to make a column with let's say 12 unique numbers, next col various days aging oldest on top, next col, 3 people , next col first person to receive 5 cases, next 4, next 3, them develop a formula on the gory cold to assign by aging, where 1st person would be in all 5 rotations, the last person eliminated after 3 rotations and the middle after 4 rotations by aging. I could just them apply the formula to larger numbers, because regardless of column size it will be based on the content of the 4 cold. Can you do that?
Expert:  The-PC-Guy replied 1 year ago.
what i need to see is how you want the output arranged
Customer: replied 1 year ago.
Like the second screen pic I sent you. Showing the assignments next to the peoples names in each rotation, by age. Thanks
Expert:  The-PC-Guy replied 1 year ago.
we are miscommunication somehow. I will need to see the actual file of the output you desire, or will be unable to help further.
Customer: replied 1 year ago.
Ok, I just sent to***@******.*** asking to forward to you. Thx
Expert:  The-PC-Guy replied 1 year ago.
ok, I got the file, so you want to take the values from column A and B and add the names in column C in order, with 1 name going to each number in column A. And then repeat the names until all of the numbers in column A have a name assigned to them?
Customer: replied 1 year ago.
I have all the preceding descriptions. But here we go. All the people in col c should receive the assignments from col a, which are arranged by their age, represented in col b. ,col d represents the number of assignments each person should receive. All assignment rotations should be in order of oldest first, essentially plugging in the list of names down the aging list and dropping each person off the rotations as you go down, once they have reached their assign limit. Thanks
Expert:  The-PC-Guy replied 1 year ago.
so they are not getting assigned equally. its based on the numbers from column D
Customer: replied 1 year ago.
Yes, however oldest first, so instead of giving them their individual assignments in a row down the list, they would be part of a rotation with the others, all with equal amounts of aged assignments going down the list in col a
Customer: replied 1 year ago.
Look at results tab of spread for idea, thx
Customer: replied 1 year ago.
I need a formula or macro that I can continuously plug in when I assign in future dates, using a constant set up format
Expert:  The-PC-Guy replied 1 year ago.
i am sending an additional service offer to cover the cost of creating this complicated macro.
Customer: replied 1 year ago.
Ok just a formula or pivot then. Thanks
Expert:  The-PC-Guy replied 1 year ago.
The only thing that will work in this case is a macro
Customer: replied 1 year ago.
Nope sorry, just provide me with a formula/pivot to apply then. Thanks
Expert:  The-PC-Guy replied 1 year ago.
like, i said the only thing that will work is a macro. There is no sufficient formula or pivot that will accomplish what you want.
I can knock \$25 off the price if budget is really tight
Expert:  The-PC-Guy replied 1 year ago.
i think I finally understand what you want to do, but there is no formula or pivot that can do it.
I can knock \$25 off the price if budget is really tight
Customer: replied 1 year ago.
If you can knock off the\$25 plus arrange the \$42 the last guy auto charged my card, to only tell me he can't help me, then I'll do it. Whet can customer service do about that last charge? Thanks
Expert:  The-PC-Guy replied 1 year ago.
i'm not exactly sure how customer service does the charges you can speak to them about that
Expert:  The-PC-Guy replied 1 year ago.
i lowered the additional service by \$25
Expert:  The-PC-Guy replied 1 year ago.
I am just following up with you to see if you are still interested in doing this,
You did not accept the additional service, and I did not hear back from you.
Should I assume you are no longer interested in this?