Little Problem, Difficult and Hard.

[MFS]

Hi All,

I have a little problem, but it is making my daily assignments too difficult
and hard to be applied, so please help me, I appreciate your support.
Every day I extract data (entries) from the system as Text and I convert it
to Excel, the data contains about 8 different codes, each code has its
amount, date, country, customer name, product’s key, seller name, and
payment’s type.
I have to put each code’s data in a separated sheet, to get this I created
some tables by using IF function.

To not to put a lot of data and to make it easy for you to help me, I putted
this example:

I have two codes in sheet1, column A contains the code (R343, or T521), and
column B contains the amount.
I want to have each code’s data in a separated sheet, so I prepared tables
in Sheet2 (R343) and Sheet 3 (T521), and I used IF function, please check the
following explanations.

1. Sheet2 (R343):
In sheet2 I prepared a table to give me only R343’s data:
Sheet2,A2: =IF(Sheet1!$A2=â€R343â€,Sheet1!A2,0). Which means if A2 in Sheet1
is “R343â€, give me A2 in Sheet1 (the code), but if not give me zero “0â€.
Sheet2,B2: =IF(Sheet1!$A2=â€R343â€,Sheet1!B2,0). Which means if A2 in Sheet1
is “R343â€, give me B2 in Sheet1 (the amount), but if not give me zero “0â€.

2. Sheet 3 (T521):
In sheet3 I prepared a table to give me only T521’s data:
Sheet3,A2: =IF(Sheet1!$A2=â€T521â€,Sheet1!A2,0). Which means if A2 in Sheet1
is “T521â€, give me A2 in Sheet1 (the code), but if not give me zero “0â€.
Sheet3,B2: =IF(Sheet1!$A2=â€T521â€,Sheet1!B2,0). Which means if A2 in Sheet1
is “T521â€, give me B2 in Sheet1 (the amount), but if not give me zero “0â€.

The problem is that wherever the cell does not match the code, a zero will
appear. I want to have only the data that matches the condition without
having any zero as a result. In other words, in Sheet2,A2 if A2 in Sheet1
does not match “R343â€, I do not want to have zero, but I want it to skip this
and check in the other cell (A3 in Sheet1).
As a summary, I do not want to have zero in the table, If the result is
false, I do not to have zero. But instead I want it to check the cell after
it.
Maybe Auto-Filter would help to remove the false results, but I am trying to
find a good solution because I have fixed files for each code, each file
should be updated according to the new text file that I extract from the
system, so I am trying to link the fixed files to the daily file.

I see that this may has some difficulties, but as I have mentioned I have
too much codes and data which I am working with everyday “morning ):â€. But I
believe that Excel can resolve such case.

If you suggest that I should use another function, or if there is any other
salutation for this problem, please give it me.
If you think that commutating by Email is better, please contact me at any
time (e-mail address removed).

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[keiji kounoike]

I don't know if this would match your purpose. But give it a try.
First select the worksheet your data reside and then run the macro
below. Your data sheet start at row 1 and this row must be a header row.
your data start from row 2 (A2).

Sub ExtractDatatest()
Dim acsh As Worksheet, dstsh As Worksheet
Dim rng As Range, Unirng As Range, id As Range
Dim i As Long
Dim ar

Application.ScreenUpdating = False

Set acsh = ActiveSheet
Set id = Range("A1")
Set rng = Range(id, id.End(xlDown))

rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set Unirng = rng.SpecialCells(xlCellTypeVisible)
ReDim ar(Unirng.Count - 1)
For Each r In Unirng
ar(i) = r.Value
i = i + 1
Next

For i = 1 To UBound(ar)
On Error Resume Next
Set dstsh = Nothing
Set dstsh = Worksheets(ar(i))
If dstsh Is Nothing Then
Set dstsh = Worksheets.Add(after:=Worksheets(Sheets.Count))
dstsh.Name = ar(i)
Else
dstsh.Cells.ClearContents
End If
On Error GoTo 0
acsh.Select
id.AutoFilter field:=1, Criteria1:=ar(i)
id.CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
Destination:=dstsh.Range("A1")
Next
acsh.AutoFilterMode = False
End Sub

Keiji