Insert columns based on a criteria (macro)

[frank012345]

I have a excel file and would like to insert 3 columns in between after each type.
for example

ID#_____ type
1 _____ apple
2 _____ apple
3 _____ apple
4 _____ apple
insert columnes)
5 _____oranges
6 _____oranges
7 _____oranges
insert columnes)
8 _____mango
9 _____mango
10 _____mango
insert columnes)



THANK YOU!

[stnkynts]

Do you mean columns or rows? If you meant rows this might work if your data is in column A and B respectively starting at row 1.

Please Login or Register  to view this content.

[abousetta]

I believe you can reduce

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

but I have to test this later tonight.

abousetta

[stnkynts]

You are 100% correct. My brain begins its slow spiral downward about this time on all Fridays

[abousetta]

Thanks for the confirmation. I'm running on caffeine right now and so can't test anything at the moment ... need more sleepppppp and less Excel.....

[frank012345]

Thank you stnkynts and abousetta! it worked great. i forgot to asked how to add total count (Column B) and avg total (column C) after each type. thank you guys for your help

column A column B Column C
ID# Type Total
1 APPLE 20
2 APPLE 10
3 APPLE 15
4 APPLE 20
Total Count Total Avg.

[stnkynts]

You don't need vba for count and averages. Just google search "=Count()" and "=Average()". If you are insistent on having vba do all the work I would need a lot more information (ie What is the Total Count Counting?, Total Average of what?, etc)

[frank012345]

Stnkynts,
I have 4 TYPES in column B , APPLE, ORANGES, MANGO, AND PEANUTS and Total qty in Column C.
Since the Macro youve created above is able to insert a blank rows for each type, i would like to get another macro to give me Total count in Column B and Total Sum in Column C after each separated types.

column
A____B____C
ID# Type__Total
1__APPLE___20
2__APPLE___10
3__APPLE___15
4__ APPLE__ 20

Total count in Column B, total sum in Column C
(blank rows)
6__oranges___20
7__oranges____10
8__oranges___15

Total count in Column B, total sum in Column C
(blank rows)

and so on..

Thank you for your help.Hope this make sense.

Franklin C.

[frank012345]

I have 4 TYPES in column B , APPLE, ORANGES, MANGO, AND PEANUTS and Total qty in Column C.
Can Macro provides total count on Column B, and Sum on Column C after each blank rows (after each type? for example; after Apple?).

column
A____B____C
ID# Type__Total
1__APPLE___20
2__APPLE___10
3__APPLE___15
4__ APPLE__ 20

Total count in Column B, total sum in Column C
(blank rows)
6__oranges___20
7__oranges____10
8__oranges___15

Total count in Column B, total sum in Column C
(blank rows)

and so on..

Thank you for your help.Hope this make sense.

Franklin C.

[abousetta]

Hi Franklin,

If you can put this in an workbook with before and after examples it will greatly help in creating a solution.

abousetta

[frank012345]

Before
A____B____C
ID# Type__Total
1__APPLE___20
2__APPLE___10
3__APPLE___15
4__ APPLE__ 20
Blank rows
blank rows
blank rows
5__oranges___20
6__oranges____10
7__oranges___15
Blank rows
Blank rows
Blank rows
8__Lime___20
9__Lime____10
10__Lime___15
Blank Rows
Blank Rows

and This is what i want after.
A____B____C
ID# Type__Total
1__APPLE___20
2__APPLE___10
3__APPLE___15
4__ APPLE__ 20
Blank rows
Total Count (colum B) Total Sum in Colum C
blank rows
5__oranges___20
6__oranges____10
7__oranges___15
Blank rows
Total Count (colum B) Total Sum in Colum C
Blank rows
8__Lime___20
9__Lime____10
10__Lime___15
Total Count (colum B) Total Sum in Colum C
Blank Rows
Blank Rows
At the bottom of the list
Do a subtotal in Column B, and Subtotal in Column C (ALL Oranges, Apple, Lime)

Thank you
Franklin C.

[event21]

Hi -

Try this solution;

Please Login or Register  to view this content.

Header in row 1, data start in row 2

Regards,
event

[frank012345]

Thanks Event. It works !!!!
Although its inserting extra blank rows what i already have from the original list (can you take that off)?.

Thank you
Franklin C

[event21]

Hi -

All you need to do is to add another sub routine to clear the blank rows first prior to calling my script. You can search that here.

Regards,
event