Need some help in creating a formula to combine cells

[RyeGuy]

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!

 

[nithin shettigar]

	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]

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]

Here is a link to data in drive https://docs.google.com/spreadsheets/d/1dR9fe7l3WemEMIzER3o-xdU0DI802VYT0DRlHMrXO44/edit?usp=sharing

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.

 

[sunny102]

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?

Here is a link to data in drive https://docs.google.com/spreadsheets/d/1dR9fe7l3WemEMIzER3o-xdU0DI802VYT0DRlHMrXO44/edit?usp=sharing

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]

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]

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]

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]

Then that would be possible via VBA only

 

[RyeGuy]

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.