Extracting unique text records across multiple sheets

[VIKBIL]

I am using Excel 2007 and I have the following query:-
I have three excel sheets(AB,XY and MN) in a workbook. In column A of each of these sheets there are certain names appearing. Further name appearing in any one of three sheet can also appear in any other sheet and vice versa. Also number of names in column A of each of the sheets are not uniform, meaning AB may have 5 names, XY may have 7 names and so on.
Against column A in each of three worksheet, I have data in column C(1Q10),E(2Q10) and G(3Q10) which are in numbers only. Further this data will get updated every quarter.
I want to have a sheet 4 with following information from the three sheets:-
1) Column A in sheet 4 which will have unique list of names from all the three sheets
2) Column B,C,D will have data points from sheet AB,XY and MN showing data for any one quarter.
3) A dropdown box in cell E2 in sheet 4 which will have drop down list as 1Q10,2Q10,3Q10 and so on. If I select say 2Q10 in drop down box, column B,C,D in sheet 4 should get updated and so on. If column A does not get updated in sheet 4 no issue.
I do not want to use any macros. It would be better if the information can be extracted using formulas only.
Regards,
VIKBIL

 

[venkat1926]

this macro gives how to get unique values form three sheets.
once you get the unq=unique values in sheet 4 use vlookup to get the data from the other sheets. I am sure you can work on it. if there is problem post back
DO THIS EXPERIMENT ON A COPY OF YOUR WORKBOOK AND NOT ON THE ORIGINAL WORKBOOK

Sub test()
Dim j As Integer, r As Range, k As Integer
Application.DisplayAlerts = False
On Error GoTo proceed
Worksheets("sheet4").Delete
proceed:
Worksheets.Add
ActiveSheet.Name = "sheet4"
For j = 1 To Worksheets.Count
If Worksheets(j).Name <> "sheet4" Then
With Worksheets(j)
Set r = Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
r.Copy Worksheets("sheet4").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End If
Next j
With Worksheets("sheet4")
.Range("A1") = "heading"
Set r = Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
r.AdvancedFilter xlFilterInPlace, , , True
End With
With Worksheets("sheet4")
For k = .Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Rows(.Cells(k, 1).Row).Hidden = True Then
Cells(k, 1).EntireRow.Delete
End If
Next k
End With
Application.DisplayAlerts = True
End Sub

 

[GaryWMn]

This will require a little setup, but you can use a multiple ranges type of PivotTable to accomplish this in "3 (relatively) easy steps":

1. Name the data ranges using an offset formula that will expand to accommodate your data.
2. Create a PivotTable based on these multiple ranges.
3. Refresh your data as needed.

I assume your ranges are set up something like this...

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Col2</td><td style="font-weight: bold;;">1Q10</td><td style="font-weight: bold;;">Col3</td><td style="font-weight: bold;;">2Q10</td><td style="font-weight: bold;;">Col4</td><td style="font-weight: bold;;">3Q10</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Fred</td><td style="text-align: right;;"></td><td style="text-align: right;;">171</td><td style="text-align: right;;"></td><td style="text-align: right;;">148</td><td style="text-align: right;;"></td><td style="text-align: right;;">126</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Mary</td><td style="text-align: right;;"></td><td style="text-align: right;;">137</td><td style="text-align: right;;"></td><td style="text-align: right;;">192</td><td style="text-align: right;;"></td><td style="text-align: right;;">163</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Bill</td><td style="text-align: right;;"></td><td style="text-align: right;;">191</td><td style="text-align: right;;"></td><td style="text-align: right;;">198</td><td style="text-align: right;;"></td><td style="text-align: right;;">194</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">AB</p><br /><br />

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Col2</td><td style="font-weight: bold;;">1Q10</td><td style="font-weight: bold;;">Col3</td><td style="font-weight: bold;;">2Q10</td><td style="font-weight: bold;;">Col4</td><td style="font-weight: bold;;">3Q10</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Fred</td><td style="text-align: right;;"></td><td style="text-align: right;;">134</td><td style="text-align: right;;"></td><td style="text-align: right;;">166</td><td style="text-align: right;;"></td><td style="text-align: right;;">111</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Joe</td><td style="text-align: right;;"></td><td style="text-align: right;;">173</td><td style="text-align: right;;"></td><td style="text-align: right;;">183</td><td style="text-align: right;;"></td><td style="text-align: right;;">133</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Bill</td><td style="text-align: right;;"></td><td style="text-align: right;;">154</td><td style="text-align: right;;"></td><td style="text-align: right;;">198</td><td style="text-align: right;;"></td><td style="text-align: right;;">136</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Sue</td><td style="text-align: right;;"></td><td style="text-align: right;;">197</td><td style="text-align: right;;"></td><td style="text-align: right;;">114</td><td style="text-align: right;;"></td><td style="text-align: right;;">186</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">XY</p><br /><br />

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Col2</td><td style="font-weight: bold;;">1Q10</td><td style="font-weight: bold;;">Col3</td><td style="font-weight: bold;;">2Q10</td><td style="font-weight: bold;;">Col4</td><td style="font-weight: bold;;">3Q10</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Fred</td><td style="text-align: right;;"></td><td style="text-align: right;;">118</td><td style="text-align: right;;"></td><td style="text-align: right;;">166</td><td style="text-align: right;;"></td><td style="text-align: right;;">172</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Mary</td><td style="text-align: right;;"></td><td style="text-align: right;;">121</td><td style="text-align: right;;"></td><td style="text-align: right;;">161</td><td style="text-align: right;;"></td><td style="text-align: right;;">128</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Bob</td><td style="text-align: right;;"></td><td style="text-align: right;;">154</td><td style="text-align: right;;"></td><td style="text-align: right;;">123</td><td style="text-align: right;;"></td><td style="text-align: right;;">199</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Tom</td><td style="text-align: right;;"></td><td style="text-align: right;;">163</td><td style="text-align: right;;"></td><td style="text-align: right;;">138</td><td style="text-align: right;;"></td><td style="text-align: right;;">189</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">MN</p><br /><br />


Now, let's look at how to do what you want. Like I said, the setup is a bit elaborate, but since it is only a one-time setup, the tool should do what you want pretty easily going forward.

Here are the specific steps:

1. Name the data ranges using an offset formula that will expand to accommodate your data.

A. Insert a named range DataAB with the following formula:
=OFFSET(AB!$A$1,0,0,COUNTA(AB!$A:$A),COUNTA(AB!$1:$1))

B. Insert a named range DataXY with the following formula:
=OFFSET(XY!$A$1,0,0,COUNTA(XY!$A:$A),COUNTA(XY!$1:$1))

C. Insert a named range DataMN with the following formula:
=OFFSET(MN!$A$1,0,0,COUNTA(MN!$A:$A),COUNTA(MN!$1:$1))

After these ranges are named, you can use them to create a PivotTable.


2. Create a PivotTable based on these multiple ranges. Here's how...

A. Start the PivotTable wizard.
B. At Where is the data that you want to analyze?, select the option for Multiple consolidation ranges.
C. At How many page fields do you want?, select the option for I will create the page fields.
D. At the range step, use function key F3 on the keyboard to select and paste your first named range, then click the Add button to add it to the list. Repeat the F3 method for the other two ranges.
E. After your range names are added to the list, at How many page fields do you want?, select 1.
- Click the range named DataAB and type AB in the Field one: box.
- Click the range named DataXY and type XY in the Field one: box.
- Click the range named DataMN and type MN in the Field one: box.
- Click on the range names again to verify that your Field one selections were accepted.
F. Click Next in the wizard.
G. At Where do you want to put the PivotTable report?, make your choice (New worksheet should do).
H. Click Finish.

At this point, the PivotTable will look something like this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Page1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">(All)</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Count of Value</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Column</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Row</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1Q10</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">2Q10</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">3Q10</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">Col2</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">Col3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">Col4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Grand Total</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Bill</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Bob</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Fred</td><td style="text-align: right;border-left: 1px solid black;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Joe</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Mary</td><td style="text-align: right;border-left: 1px solid black;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sue</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tom</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-bottom: 1px solid black;;">1</td><td style="text-align: right;border-bottom: 1px solid black;;">1</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Grand Total</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">11</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">11</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">11</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">33</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Pivot</p><br /><br />

Now you just need to tidy it up a bit.

Continue following these steps...

I. Double-click the data field Count of Value. In the list Summarize by: select Sum. Click OK.
J. Click on the Column cell (B3) to select it and type Quarter.
K. Drag the new Quarter field up to the Page fields area.
L. Drag the Page1 field to the Column fields area.
M. Click on the pull-down next to Quarter and select 1Q10 (or your field of choice).

Your PivotTable should now look like this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Quarter</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1Q10</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sum of Value</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Page1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Row</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">AB</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">MN</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">XY</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Grand Total</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Bill</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">191</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;">154</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">345</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Bob</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;">154</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">154</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Fred</td><td style="text-align: right;border-left: 1px solid black;;">171</td><td style="text-align: right;;">118</td><td style="text-align: right;border-right: 1px solid black;;">134</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">423</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Joe</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">173</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">173</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Mary</td><td style="text-align: right;border-left: 1px solid black;;">137</td><td style="text-align: right;;">121</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">258</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sue</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">197</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">197</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tom</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;">163</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">163</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Grand Total</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">499</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">556</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">658</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1713</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Pivot</p><br /><br />

3. The next time you add data, simply refresh the PivotTable and you should see your data update automagically.

Play with this and let us know if it works for you.

Gary

 

[VIKBIL]

this macro gives how to get unique values form three sheets.
once you get the unq=unique values in sheet 4 use vlookup to get the data from the other sheets. I am sure you can work on it. if there is problem post back
DO THIS EXPERIMENT ON A COPY OF YOUR WORKBOOK AND NOT ON THE ORIGINAL WORKBOOK

Sub test()
Dim j As Integer, r As Range, k As Integer
Application.DisplayAlerts = False
On Error GoTo proceed
Worksheets("sheet4").Delete
proceed:
Worksheets.Add
ActiveSheet.Name = "sheet4"
For j = 1 To Worksheets.Count
If Worksheets(j).Name <> "sheet4" Then
With Worksheets(j)
Set r = Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
r.Copy Worksheets("sheet4").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End If
Next j
With Worksheets("sheet4")
.Range("A1") = "heading"
Set r = Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
r.AdvancedFilter xlFilterInPlace, , , True
End With
With Worksheets("sheet4")
For k = .Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Rows(.Cells(k, 1).Row).Hidden = True Then
Cells(k, 1).EntireRow.Delete
End If
Next k
End With
Application.DisplayAlerts = True
End Sub

Hi Venkat,

Thanks for your reply and taking pain in writing the full macro for my query. I ran the macro as advised by you and its running perfectly. Since I don't have much knowledge of macros so I had to take help of one of my friends to sort some minor issue.
Otherwise the macros served my purpose.

Regards,
Vikash

 

[VIKBIL]

Hi Gary,

Thanks very much for making me learn using offset function in Pivot table. Earlier I was not aware of this.
The detailed procedure given by you for my query perfectly fit into my requirement and its more dynamic in terms of usage though one time pain is there to fit the whole data.

Thanks for your help.

Regards,
Vikash

 

[GaryWMn]

Glad to hear that worked for you!

 

[VIKBIL]

Hi Gary, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Further to your reply on Extracting unique text records across multiple sheets I am facing one peculiar situation on which I need your help:-<o></o>
<o></o>
Though as per the procedure highlighted by you, I am able to get the required unique fields in one single field along with corresponding names in a single worksheet (say MASTER sheet), but what is happening when I am selecting a particular quarter say 1Q10, then in the unique fields (column A in MASTER sheet), I am also getting row labels (unique fields) for other quarters also; though in the corresponding column (e.g. in column AB or XY or MN) in the MASTER sheet I am not getting any value since that quarter does not contain any value.<o></o>
<o> </o>
What I want if suppose I select any particular quarter say 1Q10, I want unique value pertaining to that quarter only to be displayed in the row labels (that is column A in the master sheet) and also corresponding values in columns AB or XY or MN and not unique fields pertaining to other quarters.
<o> </o>
I think there are some formatting issues which need to be addressed?<o></o>
<o> </o>
Few things which might help you in sorting this out:-<o></o>
<o> </o>
1) Values in column AB or XY or MN (that is 1Q10, 2Q10, 3Q10 in respective sheets) are in percentages (e.g. 2%,3.5%, etc)
2) Values in column 2, 3 and 4 in sheet AB, XY and MN are in whole numbers (115,80,90 etc)
3) My ranges for selection of unique fields in each of sheets AB or XY or MN is from row 17 to 61. This means AB may have 5 data points, XY may have 9 data points and MN may have say 4 data points.
4) In the pivot table in the master sheet, the data points (corresponding to column AB or XY or MN) have been formatted as percentages e.g. 1%,2%,5% etc.(using format cells/number/percentage)
Regards,
Vikash

 

[GaryWMn]

To help me better understand your question, can you provide samples of your data and the PivotTable you generated? (See the forum's FAQ for information on how to post copies of your data.)

 

[VIKBIL]

To help me better understand your question, can you provide samples of your data and the PivotTable you generated? (See the forum's FAQ for information on how to post copies of your data.)

Hi Gary,

I was able to resolve the doubts which I was having. Some error on my part while building the pivot table.

Regards,
Vikash