How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask R. Michael Your Own Question

R. Michael
R. Michael, MIS Analyst
Category: Programming
Satisfied Customers: 59
Experience:  Build Reports and Database Administration. MS Office and VBA are Primary skills. I also work in PHP, JavaScript, HTML, MySQL.
71693728
Type Your Programming Question Here...
R. Michael is online now
A new question is answered every 9 seconds

I have a column with 20 cells that looks like this: GUSTAVO

Customer Question

I have a column with 20 cells that looks like this:
GUSTAVO
GUSTAVO
GUSTAVO
GUSTAVO
DILLON
DILLON
DILLON
JAKE
JAKE
FRANK
FRANK
BOB
BILL
BILL
BILL
BILL
BILL
BILL
empty cell
empty cell
I need to assign the values to another column with 10 adjacent merged cells like this:
GUSTAVO
GUSTAVO
DILLON
DILLON
JAKE
FRANK
BOB
BILL
BILL
BILL
The rules are that for every 2 or 1 times a value appears in column 1, it shows up in column 2. for instance, Dillon is in 3 cells in column 1 and 2 cells in column 2. Bob is in 1 cell in column 1 and 1 cell in column 2. etc.
I hope this makes sense.
I tried to use "IF", but it became so complex that I couldn't follow all the criteria. Is there an easier way to do this?
Submitted: 1 year ago.
Category: Programming
Customer: replied 1 year ago.
each value in the 20 cells column A has this many appearances in the 10 cells in column B
2
2
2
2
2
2
2
1
1
1
1
1
3
3
3
3
3
3
1
1
Expert:  R. Michael replied 1 year ago.
Hi,
My Name is*****
Thank you for choosing Just Answer! I am pretty sure you are needing a Countif function to complete this. I was unsure of what the end result you are needing, however below is the formula for the counts of cells in each column. You can delete the columns that you do not need counts for(I entered all 4 possible counts in column C-F). Let me know if this is the solution you needed.
Thank You
Expert:  R. Michael replied 1 year ago.
Link to file with formula:
https://drive.google.com/file/d/0B3hbJgQW_-OIdS1xa0YwRVdlNmc/view?usp=sharing
Customer: replied 1 year ago.
It was a nice effort, but it still doesn't accomplish the goal of automatically populating column B with the names from column A. To be more clear with my problem, under the rules stated - the names in column A can be in any order (although they will be in groups like "Bill, Bill, Bill, Dan, Dan, Gustavo, Gustavo etc...) and may appear any number of times ("Bill, Bill, Bill, Bill, Bill"). Column B is where the names will appear once for every 2 times they appear in column A - and - once for every single occurrence or remainder of occurrences divided by 2. Please see the file attached
Expert:  R. Michael replied 1 year ago.
Ok ok, apologies, I misunderstood. That can be done as well. VBA would be the easiest way to accomplish this. Are you ok with a macro? Or do you need it to be in cell functions in column B. Just to clarify in example above Bill is listed 5 times in column a. Would you want the name 2 or 3 times in column B since 5/2 is 2.5?
Expert:  R. Michael replied 1 year ago.
Disregard 2 question about bill showing 5 times I see the rules in the spreadsheet attached.. Let me know about the macro though. I can even make it so when a name is ***** ***** column be it automatically applies the rules and enters names in column B.
Customer: replied 1 year ago.
I would be interested in VBA. I have a very basic knowledge about macros, but I am interested in expanding my skills. Thank you very much. I really appreciate your help.
Expert:  R. Michael replied 1 year ago.
Sure thing, I should be able to get you a solution by tomorrow.
Expert:  R. Michael replied 1 year ago.
Hi,
I uploaded the file to link below
Right Click Sheet1 and select "View Code", to view code and read comments
Any Cells changed in Column A of Sheet1 will apply the rules you have in sheet2.
Also I input comments in the code(Green), to help with understanding it.
It was fairly complex considering the solution needed, however I believe an in cell function would not be feasible.
Link to file: https://drive.google.com/file/d/0B3hbJgQW_-OIaFJNa3htNGZtVTg/view?usp=sharing
Customer: replied 1 year ago.
I'm not sure how to implement it. Excel won't let me call the function. I think you're on the right track.Just to be sure, I have added more explanation to the file and called it a copy.
This is a hard problem to explain, but I think it will be more clear.
Customer: replied 1 year ago.
Here's the file
Expert:  R. Michael replied 1 year ago.
Appologies I should have explained better. You don't need to call the function. Just add names to the Sheet1 of column A(Of Sheet1) and they will automatically appear in Column B. The function is called automatically when you change any cell in Column A. Be sure of the following:
Has to be an .xlsm file(Macro Enabled)
After you open if there is yellow bar at top, click enable editing, then Enable Content.(Sometimes excel is read only after download.)
If all above fails make sure Macros are enabled (File-->Options--->Trust Center)
I've re uploaded clearing names in col A so you can give another try.
Link to file: https://drive.google.com/file/d/0B3hbJgQW_-OITC1KMXFSV2oyOHc/view?usp=sharing