Formatting by matching columns A and B
I am trying to format my data by state. I have listed in column A all the states i need, but the data I have each month do not always have all the states. The format of my data is:
Column A (list of states) Column B (state in that month) Column C (#) Column D ($)
So I wish to format by looking up the states listed in column B and comparing the the complete list of states in Column A, inserting a blank row if a state is missing for that month. Is there anyway to format this quickly in excel?
Last edited by yunc1008; 10-10-2010 at 07:54 PM.
I would recommend the VLOOKUP function (see Excel help for details).
If you need the row to appear blank instead of getting a #N/A, you can nest this inside an IF function.
E.g.
Please Login or Register to view this content.
You could insert two blank rows above your data, and then move your totals to the top like so:
Then select A4, B4, E4, & H4, and enter Keys in the Names box, left of the formula bar.Please Login or Register to view this content.
Then unzip the attachment to someplace convenient, open the VBE, and drag xlAlignKeys.bas to the VBA project for the workbook.
Then do Alt+F8, run AlignKeys to see this result, in part:
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Hi ync1008,
You have a perfect Pivot Table problem. The problem lies with having your data as you do. See the attached. I moved your data to Sheet2 and made it a table. Then used Pivot Tables to show exactly what you want.
I hope this helps.
Thank you all for your quick responses! This definitely saves me a lot of time trying to figure and sort data by myself =]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks