Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate help
Hi,
I have two columns in the sheet and the data in the A & B column: Column A has few store numbers and column B has the store number data A B 12345 sjfdiasdhfajpfjaofoafoafvoa 12345 ikgiureingflsdnonolsn 12345 fiueshoifjowejfpweoefawjf 45678 sdfsmnfswfofoinionsio 45678 rgsfejfpjwejfpewjpfjwp 45678 gremgoperokpgkerpgpermgpomp 45678 ngofjosijfjwsfjpofpfp 45678 ofojosnfosnofnosfjpspjmps I will do the Advanced filter and take the unique records from the Column A to Column C. The data according to the store number in column A & column B should sit in the D column in one cell for the entire store number of column C. Let me know if it can work with the concatenate function or vlookup or parenthesis has to be used to get the results. Do the needful at the earliest. Regards, Igneshwara reddy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate help
D1:
12345 E1: =IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$D$ 1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8 ,SMALL(IF($A$1:$A$8=$D$1,ROW($A$1:$A$8)),ROW(1:1)) ,2)) Commit this function with Ctrl + Shift + Enter Hope this gives you what you need... "Igneshwara reddy" wrote: Hi, I have two columns in the sheet and the data in the A & B column: Column A has few store numbers and column B has the store number data A B 12345 sjfdiasdhfajpfjaofoafoafvoa 12345 ikgiureingflsdnonolsn 12345 fiueshoifjowejfpweoefawjf 45678 sdfsmnfswfofoinionsio 45678 rgsfejfpjwejfpewjpfjwp 45678 gremgoperokpgkerpgpermgpomp 45678 ngofjosijfjwsfjpofpfp 45678 ofojosnfosnofnosfjpspjmps I will do the Advanced filter and take the unique records from the Column A to Column C. The data according to the store number in column A & column B should sit in the D column in one cell for the entire store number of column C. Let me know if it can work with the concatenate function or vlookup or parenthesis has to be used to get the results. Do the needful at the earliest. Regards, Igneshwara reddy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate help
Do you have a header row e.g. Store_name and Data in row 1 ?. Also, is
your data sorted by store name, or if not then could it be? If the answer to both questions is yes, then you can do the following: In C2 enter this formula and copy down: =IF(A2=A3,"",A2) This will give you the store number only for the last row of each block of store numbers. In D2 enter this formula and copy down: =IF(A2=A1,D1&B2,B2) This will append the data from column B for contiguous blocks of store numbers - for each new store number it will start again. Then highlight columns C and D, click <copy then Edit | Paste Special | Values (check) | OK then <Enter. Copy the headings from A1:B1 into C1:D1. Now choose Data | Filter | Autofilter (check) and from the filter pull- down on column C select <blanks. Highlight all the visible rows (excluding the header row) and Edit | Delete Row. Select All from the filter pull-down on column C, then remove the filters by Data | Filter | Autofilter (uncheck). Finally, delete columns A and B, and you should end up with what you want. Hope this helps. Pete On Aug 29, 4:54 pm, Igneshwara reddy wrote: Hi, I have two columns in the sheet and the data in the A & B column: Column A has few store numbers and column B has the store number data A B 12345 sjfdiasdhfajpfjaofoafoafvoa 12345 ikgiureingflsdnonolsn 12345 fiueshoifjowejfpweoefawjf 45678 sdfsmnfswfofoinionsio 45678 rgsfejfpjwejfpewjpfjwp 45678 gremgoperokpgkerpgpermgpomp 45678 ngofjosijfjwsfjpofpfp 45678 ofojosnfosnofnosfjpspjmps I will do the Advanced filter and take the unique records from the Column A to Column C. The data according to the store number in column A & column B should sit in the D column in one cell for the entire store number of column C. Let me know if it can work with the concatenate function or vlookup or parenthesis has to be used to get the results. Do the needful at the earliest. Regards, Igneshwara reddy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate help
Hi,
Thanks for the solution but the column B data what ever is there to particular store number has to be in the column D. In the ex: below for store # 12345 the Columnn B data has 3 lines and this entire data has to sit in the Column D in one cell. In the same way for store # 45678 the Column B data has five lines and it has to sit in one cell of column D. I hope this will clearly explains what I require as the results. Thanks Igneshwara reddy "RyGuy" wrote: D1: 12345 E1: =IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$D$ 1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8 ,SMALL(IF($A$1:$A$8=$D$1,ROW($A$1:$A$8)),ROW(1:1)) ,2)) Commit this function with Ctrl + Shift + Enter Hope this gives you what you need... "Igneshwara reddy" wrote: Hi, I have two columns in the sheet and the data in the A & B column: Column A has few store numbers and column B has the store number data A B 12345 sjfdiasdhfajpfjaofoafoafvoa 12345 ikgiureingflsdnonolsn 12345 fiueshoifjowejfpweoefawjf 45678 sdfsmnfswfofoinionsio 45678 rgsfejfpjwejfpewjpfjwp 45678 gremgoperokpgkerpgpermgpomp 45678 ngofjosijfjwsfjpofpfp 45678 ofojosnfosnofnosfjpspjmps I will do the Advanced filter and take the unique records from the Column A to Column C. The data according to the store number in column A & column B should sit in the D column in one cell for the entire store number of column C. Let me know if it can work with the concatenate function or vlookup or parenthesis has to be used to get the results. Do the needful at the earliest. Regards, Igneshwara reddy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate help
Hi Pete,
Your forumla really helped me, but there is only one step I need more. the text which is coming in the cell is converted to the paragraph but it should be displayed inthe excel sheet in the rows wise. The text should not convert to the paragraph but it should remain as it is in a single cell. for Ex: If I have 5 lines in text in different rows it should not merge in the cell it should be in a separate lines in one cell. I hope you can definitely help me on this. Thanks in advance and waiting for your reply. Regards, Igneshwara reddy "Pete_UK" wrote: Do you have a header row e.g. Store_name and Data in row 1 ?. Also, is your data sorted by store name, or if not then could it be? If the answer to both questions is yes, then you can do the following: In C2 enter this formula and copy down: =IF(A2=A3,"",A2) This will give you the store number only for the last row of each block of store numbers. In D2 enter this formula and copy down: =IF(A2=A1,D1&B2,B2) This will append the data from column B for contiguous blocks of store numbers - for each new store number it will start again. Then highlight columns C and D, click <copy then Edit | Paste Special | Values (check) | OK then <Enter. Copy the headings from A1:B1 into C1:D1. Now choose Data | Filter | Autofilter (check) and from the filter pull- down on column C select <blanks. Highlight all the visible rows (excluding the header row) and Edit | Delete Row. Select All from the filter pull-down on column C, then remove the filters by Data | Filter | Autofilter (uncheck). Finally, delete columns A and B, and you should end up with what you want. Hope this helps. Pete On Aug 29, 4:54 pm, Igneshwara reddy wrote: Hi, I have two columns in the sheet and the data in the A & B column: Column A has few store numbers and column B has the store number data A B 12345 sjfdiasdhfajpfjaofoafoafvoa 12345 ikgiureingflsdnonolsn 12345 fiueshoifjowejfpweoefawjf 45678 sdfsmnfswfofoinionsio 45678 rgsfejfpjwejfpewjpfjwp 45678 gremgoperokpgkerpgpermgpomp 45678 ngofjosijfjwsfjpofpfp 45678 ofojosnfosnofnosfjpspjmps I will do the Advanced filter and take the unique records from the Column A to Column C. The data according to the store number in column A & column B should sit in the D column in one cell for the entire store number of column C. Let me know if it can work with the concatenate function or vlookup or parenthesis has to be used to get the results. Do the needful at the earliest. Regards, Igneshwara reddy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate help
I think you mean that you want a line feed between different items of
data. If so, amend the formula for D2 as follows: =IF(A2=A1,D1&chr(10)&B2,B2) Format this cell to wrap text and then copy down, fix values, apply filter, delete rows etc. Hope this helps. Pete On Aug 29, 7:48 pm, Igneshwara reddy wrote: Hi Pete, Your forumla really helped me, but there is only one step I need more. the text which is coming in the cell is converted to the paragraph but it should be displayed inthe excel sheet in the rows wise. The text should not convert to the paragraph but it should remain as it is in a single cell. for Ex: If I have 5 lines in text in different rows it should not merge in the cell it should be in a separate lines in one cell. I hope you can definitely help me on this. Thanks in advance and waiting for your reply. Regards, Igneshwara reddy "Pete_UK" wrote: Do you have a header row e.g. Store_name and Data in row 1 ?. Also, is your data sorted by store name, or if not then could it be? If the answer to both questions is yes, then you can do the following: In C2 enter this formula and copy down: =IF(A2=A3,"",A2) This will give you the store number only for the last row of each block of store numbers. In D2 enter this formula and copy down: =IF(A2=A1,D1&B2,B2) This will append the data from column B for contiguous blocks of store numbers - for each new store number it will start again. Then highlight columns C and D, click <copy then Edit | Paste Special | Values (check) | OK then <Enter. Copy the headings from A1:B1 into C1:D1. Now choose Data | Filter | Autofilter (check) and from the filter pull- down on column C select <blanks. Highlight all the visible rows (excluding the header row) and Edit | Delete Row. Select All from the filter pull-down on column C, then remove the filters by Data | Filter | Autofilter (uncheck). Finally, delete columns A and B, and you should end up with what you want. Hope this helps. Pete On Aug 29, 4:54 pm, Igneshwara reddy wrote: Hi, I have two columns in the sheet and the data in the A & B column: Column A has few store numbers and column B has the store number data A B 12345 sjfdiasdhfajpfjaofoafoafvoa 12345 ikgiureingflsdnonolsn 12345 fiueshoifjowejfpweoefawjf 45678 sdfsmnfswfofoinionsio 45678 rgsfejfpjwejfpewjpfjwp 45678 gremgoperokpgkerpgpermgpomp 45678 ngofjosijfjwsfjpofpfp 45678 ofojosnfosnofnosfjpspjmps I will do the Advanced filter and take the unique records from the Column A to Column C. The data according to the store number in column A & column B should sit in the D column in one cell for the entire store number of column C. Let me know if it can work with the concatenate function or vlookup or parenthesis has to be used to get the results. Do the needful at the earliest. Regards, Igneshwara reddy- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate help
Thanks pete, you really helped me a lot.
You made my job easier than how I was doing. Once again thank You for your support. Regards, Igneshwara reddy "Pete_UK" wrote: I think you mean that you want a line feed between different items of data. If so, amend the formula for D2 as follows: =IF(A2=A1,D1&chr(10)&B2,B2) Format this cell to wrap text and then copy down, fix values, apply filter, delete rows etc. Hope this helps. Pete On Aug 29, 7:48 pm, Igneshwara reddy wrote: Hi Pete, Your forumla really helped me, but there is only one step I need more. the text which is coming in the cell is converted to the paragraph but it should be displayed inthe excel sheet in the rows wise. The text should not convert to the paragraph but it should remain as it is in a single cell. for Ex: If I have 5 lines in text in different rows it should not merge in the cell it should be in a separate lines in one cell. I hope you can definitely help me on this. Thanks in advance and waiting for your reply. Regards, Igneshwara reddy "Pete_UK" wrote: Do you have a header row e.g. Store_name and Data in row 1 ?. Also, is your data sorted by store name, or if not then could it be? If the answer to both questions is yes, then you can do the following: In C2 enter this formula and copy down: =IF(A2=A3,"",A2) This will give you the store number only for the last row of each block of store numbers. In D2 enter this formula and copy down: =IF(A2=A1,D1&B2,B2) This will append the data from column B for contiguous blocks of store numbers - for each new store number it will start again. Then highlight columns C and D, click <copy then Edit | Paste Special | Values (check) | OK then <Enter. Copy the headings from A1:B1 into C1:D1. Now choose Data | Filter | Autofilter (check) and from the filter pull- down on column C select <blanks. Highlight all the visible rows (excluding the header row) and Edit | Delete Row. Select All from the filter pull-down on column C, then remove the filters by Data | Filter | Autofilter (uncheck). Finally, delete columns A and B, and you should end up with what you want. Hope this helps. Pete On Aug 29, 4:54 pm, Igneshwara reddy wrote: Hi, I have two columns in the sheet and the data in the A & B column: Column A has few store numbers and column B has the store number data A B 12345 sjfdiasdhfajpfjaofoafoafvoa 12345 ikgiureingflsdnonolsn 12345 fiueshoifjowejfpweoefawjf 45678 sdfsmnfswfofoinionsio 45678 rgsfejfpjwejfpewjpfjwp 45678 gremgoperokpgkerpgpermgpomp 45678 ngofjosijfjwsfjpofpfp 45678 ofojosnfosnofnosfjpspjmps I will do the Advanced filter and take the unique records from the Column A to Column C. The data according to the store number in column A & column B should sit in the D column in one cell for the entire store number of column C. Let me know if it can work with the concatenate function or vlookup or parenthesis has to be used to get the results. Do the needful at the earliest. Regards, Igneshwara reddy- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate help
Thanks for feeding back - glad you found the method to be useful.
Pete On Aug 29, 10:36 pm, Igneshwara reddy wrote: Thanks pete, you really helped me a lot. You made my job easier than how I was doing. Once again thank You for your support. Regards, Igneshwara reddy "Pete_UK" wrote: I think you mean that you want a line feed between different items of data. If so, amend the formula for D2 as follows: =IF(A2=A1,D1&chr(10)&B2,B2) Format this cell to wrap text and then copy down, fix values, apply filter, delete rows etc. Hope this helps. Pete On Aug 29, 7:48 pm, Igneshwara reddy wrote: Hi Pete, Your forumla really helped me, but there is only one step I need more. the text which is coming in the cell is converted to the paragraph but it should be displayed inthe excel sheet in the rows wise. The text should not convert to the paragraph but it should remain as it is in a single cell. for Ex: If I have 5 lines in text in different rows it should not merge in the cell it should be in a separate lines in one cell. I hope you can definitely help me on this. Thanks in advance and waiting for your reply. Regards, Igneshwara reddy "Pete_UK" wrote: Do you have a header row e.g. Store_name and Data in row 1 ?. Also, is your data sorted by store name, or if not then could it be? If the answer to both questions is yes, then you can do the following: In C2 enter this formula and copy down: =IF(A2=A3,"",A2) This will give you the store number only for the last row of each block of store numbers. In D2 enter this formula and copy down: =IF(A2=A1,D1&B2,B2) This will append the data from column B for contiguous blocks of store numbers - for each new store number it will start again. Then highlight columns C and D, click <copy then Edit | Paste Special | Values (check) | OK then <Enter. Copy the headings from A1:B1 into C1:D1. Now choose Data | Filter | Autofilter (check) and from the filter pull- down on column C select <blanks. Highlight all the visible rows (excluding the header row) and Edit | Delete Row. Select All from the filter pull-down on column C, then remove the filters by Data | Filter | Autofilter (uncheck). Finally, delete columns A and B, and you should end up with what you want. Hope this helps. Pete On Aug 29, 4:54 pm, Igneshwara reddy wrote: Hi, I have two columns in the sheet and the data in the A & B column: Column A has few store numbers and column B has the store number data A B 12345 sjfdiasdhfajpfjaofoafoafvoa 12345 ikgiureingflsdnonolsn 12345 fiueshoifjowejfpweoefawjf 45678 sdfsmnfswfofoinionsio 45678 rgsfejfpjwejfpewjpfjwp 45678 gremgoperokpgkerpgpermgpomp 45678 ngofjosijfjwsfjpofpfp 45678 ofojosnfosnofnosfjpspjmps I will do the Advanced filter and take the unique records from the Column A to Column C. The data according to the store number in column A & column B should sit in the D column in one cell for the entire store number of column C. Let me know if it can work with the concatenate function or vlookup or parenthesis has to be used to get the results. Do the needful at the earliest. Regards, Igneshwara reddy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with CONCATENATE! | Excel Discussion (Misc queries) | |||
Concatenate | Excel Discussion (Misc queries) | |||
CONCATENATE | Excel Worksheet Functions | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
Un - Concatenate? | Excel Worksheet Functions |