Concatenate help

[Igneshwara reddy]

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

 

[RyGuy]

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

 

[Pete_UK]

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
C11.

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

 

[Igneshwara reddy]

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

 

[Igneshwara reddy]

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]

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

 

[Igneshwara reddy]

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]

Thanks for feeding back - glad you found the method to be useful.

Pete