Consolidate mixed number and text data from multiple sheets

E

evanmarch

New Member
Joined
Sep 18, 2006
Messages
2
I have a workbook with a sheet for each month. On each monthly sheet I have created formulas that single out every line item for which I have not received payment. Each of these lines include dolar amounts, sales order number and description, all in seperate colums. I would like to create a consolidated sheet showing only these unpaid items but all in one place. Can this be done?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Hi evanmarch
Welcome to the board

2 solutions
Manual - in a month sheet use autofilter on the column that has the formulas that single out the unpaid items. Display only the unpaid items and copy them to the summary sheet

Automatic - do the same thing in vba cycling through the 12 sheets. To get started record a macro doing it manually for one month.

Hope this helps
PGC
 
Upvote 0
When I set up the macro, how do I handle the issue of varying numbers of items. Sometimes I may need to cut and paste 10 items and sometimes 50. Can a macro recognize the different lengths or would it just cut and paste theh same number of items over and over?
 
Upvote 0
Hi evanmarch

I prepared a small example. This is just to get you started. You'll have to adjust it to your case.

I tested it and I'll post the test data, part in this post and part in the next.

This is the example configuration

All worksheets have the same structure:
- the first row is a header row
- data is in columns A:D
- the values in column D (Status) single out the unpaid items
- for each item, column D is never blank, either has "Unpaid" or something else

The data is in the 3 first worksheets.
The result is in the worksheet "Summary"
The code always starts with a clean "Summary" sheet (clears contents).

I hope this helps you to build your solution
PGC

Code: 
Option Explicit

Sub CopyUnpaid()
Dim wksSumm As Worksheet, wks As Worksheet, rCopyFrom As Range, i As Integer
    
Set wksSumm = Worksheets("Summary")
wksSumm.UsedRange.ClearContents
wksSumm.Range("A1:D1").Value = Worksheets(1).Range("A1:D1").Value
For i = 1 To 3
    Set wks = Worksheets(i)
    wks.Columns("D").AutoFilter Field:=1, Criteria1:="Unpaid"
    Set rCopyFrom = wks.Range("A2:D" & wks.Range("D" & wks.Rows.Count).End(xlUp).Row)
    If rCopyFrom.Row<> 1 Then _
        rCopyFrom.SpecialCells(xlCellTypeVisible).Copy Destination:= _
            wksSumm.Range("A" & wksSumm.Range("D" & Rows.Count).End(xlUp).Row).Offset(1)
    wks.AutoFilterMode = False
Next
End Sub
Book1
ABCDE
1CodeItemPriceStatus
2ID001Chair4.5Unpaid
3ID002Table16.5Paid
4ID003Book3.1Paid
5ID004PC134Unpaid
6ID005CD23Unpaid
7ID006scissor11Paid
8ID007paper8Unpaid
9ID008Glue23Paid
10ID009Clips2Unpaid
11
12
Sheet1
Book1
ABCDE
1CodeItemPriceStatus
2ID0010Pencil4.5Paid
3ID0011Rubber2Paid
4ID0012Pencil3.1Paid
5
6
Sheet2
 
Upvote 0
And here goes the last data worksheet and the Summary worksheet that was built automatically by running the code.
Book1
ABCDE
1CodeItemPriceStatus
2ID0020Car50Unpaid
3ID0021Bike20Paid
4ID0022Boat40Unpaid
5
6
Sheet3
Book1
ABCDE
1CodeItemPriceStatus
2ID001Chair4.5Unpaid
3ID004PC134Unpaid
4ID005CD23Unpaid
5ID007paper8Unpaid
6ID009Clips2Unpaid
7ID0020Car50Unpaid
8ID0022Boat40Unpaid
9
10
Summary
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function
2
Replies
11
Views
389
etaf
etaf
F
  • Question Question
Multiple workbooks with multiple sheets in each
Replies
1
Views
316
alansidman
alansidman
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
251
Domenic
D
J
  • Question Question
Pulling Data From ~300 Individual Sheets Onto a Master Sheet
Replies
1
Views
315
ranman256
ranman256
S
  • Question Question
Consolidating data from multiple tabs into one tab
Replies
6
Views
273
alansidman
alansidman

Forum statistics

Threads
1,225,062
Messages
6,182,634
Members
453,129
Latest member
mike4slund

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top