Need a hand with matching cells

[austin397]

I need 5 horizontal cells to automatically fill in based on the information that might be in 10 vertical cells without the 5 horizontal cells duplicating.

https://www.dropbox.com/sh/l8fcalyjlbjrlh4/AAALvRoEbTDq4tGhk5BclGURa?dl=0

The link will take you to my dopbox where you can download a sample that should clarify what I am meaning.

Thank you in advance!

 

[austin397]

I'm still hoping to get an answer on this? Can it be done? Is more information still needed?

 

[hiker95]

austin397

Your reply #10 does not show what your results (manually formatted by you) should look like?

 

[austin397]

I'm not sure what you mean, I manually entered the information that I am wanting to be automatically added. The information in the blue cells are what I would like to have auto generate based on what is in the white cells. Cells B2:F2 should generate this, when cells A6:A19 contain that.

Sorry, guess I should have know to do that as well.

Excel 2007

	A	B	C	D	E	F
1		Fab 1:	Fab 2:	Fab 3:	Fab 4:	Fab 5:
2		Tee	90 Degree	Angle Fab	4" Side Outlet
3
4	Fabs	-	-	-	-	-
5
6	Tee
7	Tee
8
9
10
11
12	90 Degree
13	Angle Fab
14
15
16	4" Side Outlet
17
18
19

<tbody>
</tbody>

sample​

Thanks!

 

[hiker95]

I manually entered the information that I am wanting to be automatically added. The information in the blue cells are what I would like to have auto generate based on what is in the white cells. Cells B2:F2 should generate this, when cells A6:A19 contain that.

austin397,

As you enter text in column A, from A6 down, here is a Worksheet_Change event that will do what you want.

Sample worksheet:

Excel 2007
	A	B	C	D	E	F
1		Fab 1:	Fab 2:	Fab 3:	Fab 4:	Fab 5:
2
3
4	Fabs	-	-	-	-	-
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
sample

And, as you add the Fab's in column A, one at a time, the final results:

Excel 2007
	A	B	C	D	E	F
1		Fab 1:	Fab 2:	Fab 3:	Fab 4:	Fab 5:
2		Tee	90 Degree	Angle Fab	4" Side Outlet
3
4	Fabs	-	-	-	-	-
5
6	Tee
7	Tee
8
9
10
11
12	90 Degree
13	Angle Fab
14
15
16	4" Side Outlet
17
18
19
20
sample

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 03/30/2017, ME981215
Dim f As Range, nc As Long
If Intersect(Target, Range("A6", Range("A" & Rows.Count).End(xlUp))) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
    If Not Target = vbEmpty Then
      Set f = Rows(2).Find(Target.Value, LookAt:=xlWhole)
      If f Is Nothing Then
        nc = Cells(2, .Columns.Count).End(xlToLeft).Column + 1
          Cells(2, nc).Value = Target.Value
          Columns(nc).AutoFit
      End If
    End If
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then add data in column A, beginning in cell A6, down, one cell at a time.

 

[austin397]

Thanks hiker95!

That works for the example, now I just need to modify the code a bit to fit with my real sheet which is much more complicated. I just needed a starting point.

 

[hiker95]

Thanks hiker95!

That works for the example. I just needed a starting point.

austin397,

Thanks for the feedback.

You are very welcome. Glad I could help.

I just need to modify the code a bit to fit with my real sheet which is much more complicated.

Just let me know if you need additional help.