M
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).
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).