| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little Problem, Difficult and Hard.
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, products key, seller name, and payments type. I have to put each codes 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 codes 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 R343s 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 T521s 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 . -- MFS22 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Little Problem, Difficult and Hard.
Check your post in public.excel.programming
-- Jim Cone Portland, Oregon USA lsmft "MFS" wrote in message 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, products key, seller name, and payments type. I have to put each codes 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 codes 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 R343s 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 T521s 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 . -- MFS22 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A Difficult Unconcatinate Problem | Excel Discussion (Misc queries) | |||
| A difficult problem - Excel 97 | Excel Discussion (Misc queries) | |||
| Difficult Excel Problem | Excel Worksheet Functions | |||
| Begineer with (seemingly) hard problem | Excel Discussion (Misc queries) | |||
| Difficult Sorting Problem | Excel Discussion (Misc queries) | |||
Linear Mode
