Insert a predefined set of values (cells, rows) into an existing table depending in value

Salem 80 · Jan 19, 2018

Good day everyone,
I would like to insert a predefined set of values (cells, rows) into an existing table , with the location of the inserted values depending on the values of the same type in the existing data table, as explained in the following table :

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 129"] Predefined values ( One column, Temperature)
[/TD]
[TD="width: 220, colspan: 3"] Existing data Table (Multiple columns, Temperature, pressure, volume, etc.…) before inserting new predefined values.

[/TD]
[TD="width: 219, colspan: 3"] New data table after predefined values are inserted
[/TD]
[/TR]
[TR]
[TD="width: 129"]

Temperature

[/TD]
[TD="width: 93"]

Temperature

[/TD]
[TD="width: 66"]

Pressure

[/TD]
[TD="width: 61"]

Volume

[/TD]
[TD="width: 93"] Temperature
[/TD]
[TD="width: 66"] Pressure
[/TD]
[TD="width: 61"] Volume
[/TD]
[/TR]
[TR]
[TD="width: 129"] 0
[/TD]
[TD="width: 93"]

1

[/TD]
[TD="width: 66"]

9

[/TD]
[TD="width: 61"]

0.253

[/TD]
[TD="width: 93"] 0
[/TD]
[TD="width: 66"]
[/TD]
[TD="width: 61"]
[/TD]
[/TR]
[TR]
[TD="width: 129"] 25
[/TD]
[TD="width: 93"]

13

[/TD]
[TD="width: 66"]

117

[/TD]
[TD="width: 61"]

3.289

[/TD]
[TD="width: 93"] 1
[/TD]
[TD="width: 66"] 9
[/TD]
[TD="width: 61"] 0.253
[/TD]
[/TR]
[TR]
[TD="width: 129"] 50
[/TD]
[TD="width: 93"]

26

[/TD]
[TD="width: 66"]

234

[/TD]
[TD="width: 61"]

6.578

[/TD]
[TD="width: 93"] 13
[/TD]
[TD="width: 66"] 117
[/TD]
[TD="width: 61"] 3.289
[/TD]
[/TR]
[TR]
[TD="width: 129"] 75
[/TD]
[TD="width: 93"]

42

[/TD]
[TD="width: 66"]

378

[/TD]
[TD="width: 61"]

10.626

[/TD]
[TD="width: 93"] 25
[/TD]
[TD="width: 66"]
[/TD]
[TD="width: 61"]
[/TD]
[/TR]
[TR]
[TD="width: 129"] 100
[/TD]
[TD="width: 93"]

69

[/TD]
[TD="width: 66"]

621

[/TD]
[TD="width: 61"]

17.457

[/TD]
[TD="width: 93"] 26
[/TD]
[TD="width: 66"] 234
[/TD]
[TD="width: 61"] 6.578
[/TD]
[/TR]
[TR]
[TD="width: 129"]
[/TD]
[TD="width: 93"]

89

[/TD]
[TD="width: 66"]

801

[/TD]
[TD="width: 61"]

22.517

[/TD]
[TD="width: 93"] 42
[/TD]
[TD="width: 66"] 378
[/TD]
[TD="width: 61"] 10.626
[/TD]
[/TR]
[TR]
[TD="width: 129"]
[/TD]
[TD="width: 93"]

95

[/TD]
[TD="width: 66"]

855

[/TD]
[TD="width: 61"]

24.035

[/TD]
[TD="width: 93"] 50
[/TD]
[TD="width: 66"]
[/TD]
[TD="width: 61"]
[/TD]
[/TR]
[TR]
[TD="width: 129"]
[/TD]
[TD="width: 93"]

100

[/TD]
[TD="width: 66"]

900

[/TD]
[TD="width: 61"]

25.300

[/TD]
[TD="width: 93"] 69
[/TD]
[TD="width: 66"] 621
[/TD]
[TD="width: 61"] 17.457
[/TD]
[/TR]
[TR]
[TD="width: 129"]
[/TD]
[TD="width: 93"]

[/TD]
[TD="width: 66"]

[/TD]
[TD="width: 61"]

[/TD]
[TD="width: 93"] 75
[/TD]
[TD="width: 66"]
[/TD]
[TD="width: 61"]
[/TD]
[/TR]
[TR]
[TD="width: 129"]
[/TD]
[TD="width: 93"]

[/TD]
[TD="width: 66"]

[/TD]
[TD="width: 61"]

[/TD]
[TD="width: 93"] 89
[/TD]
[TD="width: 66"] 801
[/TD]
[TD="width: 61"] 22.517
[/TD]
[/TR]
[TR]
[TD="width: 129"]
[/TD]
[TD="width: 93"]

[/TD]
[TD="width: 66"]

[/TD]
[TD="width: 61"]

[/TD]
[TD="width: 93"] 95
[/TD]
[TD="width: 66"] 855
[/TD]
[TD="width: 61"] 24.035
[/TD]
[/TR]
[TR]
[TD="width: 129"]
[/TD]
[TD="width: 93"]

[/TD]
[TD="width: 66"]

[/TD]
[TD="width: 61"]

[/TD]
[TD="width: 93"] 100
[/TD]
[TD="width: 66"] 900
[/TD]
[TD="width: 61"] 25.300
[/TD]
[/TR]
</tbody>[/TABLE]

Please note that the predefined value of 100 for example already exists in the raw data table, so the value in the table was not replaced.

MickG · Jan 20, 2018

Try this for results starting "E1".

Code:

[COLOR="Navy"]Sub[/COLOR] MG20Jan59
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, R1 [COLOR="Navy"]As[/COLOR] Range, R2 [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] R1 = Range("A3", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] R2 = Range("B3", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Union(R1, R2)
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]With[/COLOR] CreateObject("System.Collections.ArrayList")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Contains(Dn.Value) [COLOR="Navy"]Then[/COLOR] .Add Dn.Value
[COLOR="Navy"]Next[/COLOR] Dn
.Sort
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] R2
 [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = Dn
[COLOR="Navy"]Next[/COLOR] Dn
  
ReDim Ray(1 To .Count + 1, 1 To 3): c = 1
Ray(1, 1) = "Temperature": Ray(1, 2) = "Pressure": Ray(1, 3) = "Volume"
[COLOR="Navy"]For[/COLOR] n = 0 To .Count - 1
    c = c + 1
    [COLOR="Navy"]If[/COLOR] Dic.exists(.Item(n)) [COLOR="Navy"]Then[/COLOR]
        Ray(c, 1) = Dic(.Item(n))
        Ray(c, 2) = Dic(.Item(n)).Offset(, 1).Value
        Ray(c, 3) = Dic(.Item(n)).Offset(, 2).Value
    [COLOR="Navy"]Else[/COLOR]
        Ray(c, 1) = .Item(n)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Range("E1").Resize(c, 3)
    .Value = Ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Salem 80 · Jan 20, 2018

Thank you very much. This might sound a little strange, but can this be transformed in some way to a formula?

Salem 80 · Feb 8, 2018

Dear MickG,

Thank you for your post.
I inserted the code in my workbook and run the code, but got the following message:

Run-time error '-2146233079 (80131509}':
Failed to compare two elements in the array.

Kind regards

Salem

MickG · Feb 8, 2018

See if this works for you !!
Results start "E2"
https://app.box.com/s/ygotpeot7pi0e9gimjvjxw1qjqe1dvo1

Salem 80 · Feb 13, 2018

Thank you Mr. MickG,

Its working perfectly.
 Would it be possible to automatically run the code without the step of requesting this action. In the sense that the table will automatically insert the predefined values?

With my best regards

Salem
[FONT=&quot][/FONT]

MickG · Feb 14, 2018

You could get the code to run when you insert/change data in column "A", would that do, or another perhaps Insert/change in another column. What do you think ?????

Salem 80 · Feb 28, 2018

Thank you very very much.
that works perfectly, and sorry for the late reply.

MickG · Mar 1, 2018

You're welcome

Insert a predefined set of values (cells, rows) into an existing table depending in value

Salem 80

New Member

Excel Facts

MickG

MrExcel MVP

Salem 80

New Member

Salem 80

New Member

MickG

MrExcel MVP

Salem 80

New Member

MickG

MrExcel MVP

Salem 80

New Member

MickG

MrExcel MVP

Similar threads

Forum statistics

Share this page

Insert a predefined set of values (cells, rows) into an existing table depending in value

New Member

Excel Facts

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock