# Need some help in creating a formula to combine cells

#### RyeGuy

##### New Member
Basically I have a data set that runs across 40+ columns. Its tough for me to explain what it is I am trying to do so the example below hopeful gives some pictoral to it.

As new data gets added I need to pull them int a separate sheet to list specific columns.

1 ABCDEFGHIJKLMNOPQESTUVWXYZ
2 ABCDEFGHIJKLMNOPQESTUVWXYZ
3 ABCDEFGHIJKLMNOPQESTUVWXYZ
4 ABCDEFGHIJKLMNOPQESTUVWXYZ

1 ABCD
2 LMNO
3 WXYZ
4 ABCD

Any help here would be greatly appreciated. Happy Holidays!

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### nithin shettigar

##### New Member
 C D E 3 3 ABCDEFGHIJKLMNOPQESTUVWXYZ 1 ABCD 4 ABCDEFGHIJKLMNOPQESTUVWXYZ 2 LMNO 5 ABCDEFGHIJKLMNOPQESTUVWXYZ 3 WXYZ 6 ABCDEFGHIJKLMNOPQESTUVWXYZ 1 ABCD 7 ABCDEFGHIJKLMNOPQESTUVWXYZ 2 LMNO 8 ABCDEFGHIJKLMNOPQESTUVWXYZ 3 WXYZ 9 ABCDEFGHIJKLMNOPQESTUVWXYZ 1 ABCD 10 ABCDEFGHIJKLMNOPQESTUVWXYZ 2 LMNO

<tbody>
</tbody>

D3 = IF(D2=3,1,IF(D2=1,2,IF(D2=2,3)))
E3 =IF(D3=1,LEFT(TRIM(C3),4),IF(D3=2,MID(TRIM(C3),12,4),IF(D3=3,RIGHT(TRIM(C3),4),"")))

#### sunny102

##### Board Regular
Hi,

Can you explain a bit more or can you share your file here, it'll be easier to understand then?

I'm online and able to respond in real time to you until it's solved.

#### RyeGuy

##### New Member

If you look across multiple columns you will see

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
 Observation Notes What needs to happen based on observations? Who Must Make it Happen: Required Completion Date: What is required to make it Happen: Was Assigned Action Completed as Defined: If not, why not

<tbody>
</tbody>

There are multiple occurrences. I need to pull all this into one sheet to show a list of all deliverables.

Last edited:

#### sunny102

##### Board Regular
I am looking at the sheet now, but it is still not clear as to what output you need in that. Can you explain by giving an example?

If you look across multiple columns you will see

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
 Observation Notes What needs to happen based on observations? Who Must Make it Happen: Required Completion Date: What is required to make it Happen: Was Assigned Action Completed as Defined: If not, why not

<tbody>
</tbody>

There are multiple occurrences. I need to pull all this into one sheet to show a list of all deliverables.

#### RyeGuy

##### New Member
Sorry for the lack of clarity.

I need 1 sheet that pulls all the observation notes, what needs to happen, etc and lists them down in rows. The problem is this would be easy if the file only had one set of the above fields however the columns are repeated throughout the file. (There lies my issue).

I am trying to pull them into on sheet to simplify the few. Additionally the file where the data si coming from is not mine and I cannot manipulate the columns.

In the sheet you are looking at you see there is txt/data in Col S-Y then more in AM-AS (more if you keep going to the right)

I need to Create a new sheet that that just combines these into one set of rows that just run down

Example:

 Observation Notes What needs to happen based on observations? Who Must Make it Happen: Required Completion Date: What is required to make it Happen: Was Assigned Action Completed as Defined: If not, why not More energy Celebrate previous week success Store manager 12/18/2016 Marvin Yes Slow traffic day - did good time with ole playing and coaching during down time. Focus on individual team member kpis and how that drives store performance. Marlon and Marvin 1/31/2017 Request for Marvin to spend a week or two in Valley Stream to improve bedding process and get feel for running a high volume store. No GC to coordinate.

<colgroup><col span="7"></colgroup><tbody>
</tbody>

#### sunny102

##### Board Regular
Ok, I understand now. As the columns are fixed, so you can simply refer them in the new sheet row wise, so for example, you can refer in new sheets, A2=S of sheet1, A3 =AM and so, similarly, B2=T, B3=AN, etc.

Sorry for the lack of clarity.

I need 1 sheet that pulls all the observation notes, what needs to happen, etc and lists them down in rows. The problem is this would be easy if the file only had one set of the above fields however the columns are repeated throughout the file. (There lies my issue).

I am trying to pull them into on sheet to simplify the few. Additionally the file where the data si coming from is not mine and I cannot manipulate the columns.

In the sheet you are looking at you see there is txt/data in Col S-Y then more in AM-AS (more if you keep going to the right)

I need to Create a new sheet that that just combines these into one set of rows that just run down

Example:

 Observation Notes What needs to happen based on observations? Who Must Make it Happen: Required Completion Date: What is required to make it Happen: Was Assigned Action Completed as Defined: If not, why not More energy Celebrate previous week success Store manager 12/18/2016 Marvin Yes Slow traffic day - did good time with ole playing and coaching during down time. Focus on individual team member kpis and how that drives store performance. Marlon and Marvin 1/31/2017 Request for Marvin to spend a week or two in Valley Stream to improve bedding process and get feel for running a high volume store. No GC to coordinate.

<tbody>
</tbody>

#### RyeGuy

##### New Member
That would work however as more data is populated its is either going to overwrite or leave gaps in the list. I think I need some sort of loop.

#### sunny102

##### Board Regular
Then that would be possible via VBA only

#### RyeGuy

##### New Member
Got it. I know enough to get by in VBA but not enough to get to where I need to be. I'll keep plugging away at it as I may be able to make a creative work around for the time being. Thank you for all your help sunny.

Replies
10
Views
531
Replies
1
Views
189
Replies
2
Views
124
Replies
1
Views
186
Replies
6
Views
201

1,190,946
Messages
5,983,803
Members
439,860
Latest member
AlunM

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back