separation of data

A

alireza123456

New Member
Joined
Aug 3, 2016
Messages
23
Hi
I have a large number of factory data in different sheets and wish separate a specified factory data. please guide me.
for example:
sheet1:
[TABLE="width: 280"]
<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 70"]factory[/TD]
[TD="class: xl65, width: 70"]sale[/TD]
[TD="class: xl65, width: 70"]income[/TD]
[TD="class: xl65, width: 70"]work day[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]500[/TD]
[TD="class: xl65"]4
[/TD]
[/TR]
</tbody>[/TABLE]

sheet2:
[TABLE="width: 280"]
<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 70"]factory[/TD]
[TD="class: xl65, width: 70"]sale[/TD]
[TD="class: xl65, width: 70"]income[/TD]
[TD="class: xl65, width: 70"]work day[/TD]
[/TR]
[TR]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]500[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]h[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
</tbody>[/TABLE]

sheet 3:
[TABLE="width: 280"]
<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 70"]factory[/TD]
[TD="class: xl65, width: 70"]sale[/TD]
[TD="class: xl65, width: 70"]income[/TD]
[TD="class: xl65, width: 70"]work day[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
</tbody>[/TABLE]

i want:
[TABLE="width: 280"]
<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 70"]factory[/TD]
[TD="class: xl65, width: 70"]sale[/TD]
[TD="class: xl65, width: 70"]income[/TD]
[TD="class: xl65, width: 70"]work day[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
alireza123456 said:
Hi
I have a large number of factory data in different sheets and wish separate a specified factory data. please guide me.
for example:
sheet1:
[TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 70"]factory[/TD]
[TD="class: xl65, width: 70"]sale[/TD]
[TD="class: xl65, width: 70"]income[/TD]
[TD="class: xl65, width: 70"]work day[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]500[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
</tbody>[/TABLE]

sheet2:
[TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 70"]factory[/TD]
[TD="class: xl65, width: 70"]sale[/TD]
[TD="class: xl65, width: 70"]income[/TD]
[TD="class: xl65, width: 70"]work day[/TD]
[/TR]
[TR]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]500[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]h[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
</tbody>[/TABLE]

sheet 3:
[TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 70"]factory[/TD]
[TD="class: xl65, width: 70"]sale[/TD]
[TD="class: xl65, width: 70"]income[/TD]
[TD="class: xl65, width: 70"]work day[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
</tbody>[/TABLE]

i want:
[TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 70"]factory[/TD]
[TD="class: xl65, width: 70"]sale[/TD]
[TD="class: xl65, width: 70"]income[/TD]
[TD="class: xl65, width: 70"]work day[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
This quesion also come in :

separation of data
 
Upvote 0
Hello Alireza,

Perhaps the following code will do the task for you:-

Code: 
Sub SearchShts()

Application.ScreenUpdating = False

        Dim lr As Long
        Dim fSearch As String
        Dim ws As Worksheet
        Dim cell As Range

fSearch = Sheet1.Range("F1").Value

For Each ws In Worksheets
        If ws.Name <> "Master" Then
        
For Each cell In ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
                 If cell.Value = fSearch Then
                 lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
                 cell.EntireRow.Copy
                 Sheet1.Range("A" & lr).PasteSpecial xlPasteValues
                 Sheet1.Range("E" & lr).Value = ws.Name
                 End If
             Next
       End If
Next ws

Sheet1.Range("F1") = "SEARCH"
Sheet1.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code checks a search box in cell F1 (in the Master sheet) for a value selected from a drop down list and then searches all sheets for that value. It then transfers all the relevant data related to that value to the Master sheet.
The source sheet is also placed in Column E of the Master sheet.

Following is the link to my test work book. Select a value from the drop down in the search box then click on "GO" to see it work.

https://www.dropbox.com/s/3x4w8sniphym8mz/Alireza(multi shts to master sht).xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.
 
Upvote 0
very very thanks Cheerio
if i add a row to sheet 1 :
W 10 100 5
then w don't add to search cell in master sheet.
what is your suggestion?
:)
 
Upvote 0
Hello Alireza,

In the code above, sheet1 is the "Master" sheet so you shouldn't be adding anything to it. But I do assume that you mean the source sheets.
So, back in the Master sheet, you need to add W to the data validation drop down. It will then work.

Cheerio,
vcoolio.
 
Upvote 0
very very thanks Cheerio
if i add a row to sheet 2 :
W 10 100 5
then w don't add to search cell in master sheet.
what is your suggestion?
 
Upvote 0
As I said in post #5 above, you need to add the W to the data validation drop down list which is in cell F1 in the sample I supplied.

Cheerio,
vcoolio.
 
Upvote 0
thanks, i understand now.
pardon me. i am novic.
Did you mind if i have simple questio.
who i can use this macro in other worksheet?

please notice i'm novic. if you have time, please tell me step by step.
 
Upvote 0
Hello Alireza,

I will do that for you but later today/tonight. In the meantime, could please upload a sample of your actual work book to a free file sharing site (such as DropBox) and then post the link to your file back here. Please use dummy data.

Cheerio,
vcoolio.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Finding matching data
Replies
4
Views
126
Fluff
Fluff
M
  • Question Question
Sum together the larger amount of two ranges by row
Replies
3
Views
205
Cubist
Cubist
R
  • Solved
YTD and Period Averages? Possible averageif with index/match?
Replies
2
Views
166
reacon84
R
S
  • Question Question
Shifting cell reference
Replies
5
Views
181
Cubist
Cubist
A
  • Solved
Extract the require data from Sheet1 to Sheet2 (Total Consumption For an Item for a period in Sheet1 For Several Items) in another sheet2 get in deta
2
Replies
10
Views
315
DanteAmor
DanteAmor

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