Formula to Align Identical Rows, and Rows which match in Part

[Goldenboy23]

Dear all,

Fairly new to excel, and have been trying for several days to solve this one with no success. I have a scenario where i have several different columns (which are in reality account strings) and i am required to match them across each row. My issue is that not all rows will have information in them, and there is no one single column which will necessarily be complete. Therefore a simple V lookup will not assist. However, there are two columns (D&E) which when combined do contain all the information and i think can be used a driver for all the other columns.

Please see a simpler illustration (below) of my problem and the result i wish to achieve;

Problem;

[TABLE="width: 320"]
<colgroup><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"></colgroup><tbody>[TR]
[TD="class: et2, width: 64"]A[/TD]
[TD="class: et2, width: 64"]B[/TD]
[TD="class: et2, width: 64"]C[/TD]
[TD="class: et3, width: 64"]D[/TD]
[TD="class: et3, width: 64"]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et4, width: 64"]1[/TD]
[TD="class: et4, width: 64"][/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et1, width: 64"]2[/TD]
[TD="class: et1, width: 64"]6[/TD]
[TD="class: et4, width: 64"]3[/TD]
[TD="class: et4, width: 64"][/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]5[/TD]
[TD="class: et1, width: 64"]3[/TD]
[TD][/TD]
[TD="class: et4, width: 64"]2[/TD]
[TD="class: et4, width: 64"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]6[/TD]
[TD][/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: et1, width: 64"]5[/TD]
[TD="class: et4, width: 64"]6[/TD]
[TD="class: et4, width: 64"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: et1, width: 64"]4[/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]6[/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]7[/TD]
[TD="class: et1, width: 64"]4[/TD]
[TD][/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]7[/TD]
[/TR]
</tbody>[/TABLE]


The end result i wish to see;

[TABLE="width: 320"]
<colgroup><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"></colgroup><tbody>[TR]
[TD="class: et2, width: 64"]A[/TD]
[TD="class: et2, width: 64"]B[/TD]
[TD="class: et2, width: 64"]C[/TD]
[TD="class: et3, width: 64"]D[/TD]
[TD="class: et3, width: 64"]E[/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et4, width: 64"]1[/TD]
[TD="class: et4, width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]2[/TD]
[TD="class: et1, width: 64"][/TD]
[TD="class: et4, width: 64"]2[/TD]
[TD="class: et4, width: 64"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]3[/TD]
[TD][/TD]
[TD="class: et4, width: 64"]3[/TD]
[TD="class: et4, width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]4[/TD]
[TD="class: et1, width: 64"]4[/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]4[/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]5[/TD]
[TD="class: et1, width: 64"][/TD]
[TD="class: et1, width: 64"]5[/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]6[/TD]
[TD="class: et1, width: 64"]6[/TD]
[TD="class: et4, width: 64"]6[/TD]
[TD="class: et4, width: 64"]6[/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]7[/TD]
[TD="class: et1, width: 64"][/TD]
[TD][/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]7[/TD]
[/TR]
</tbody>[/TABLE]

I think an index and match may the solution, but i am just not quite able to fully solve it. P.S (the above is just an illustration for me to apply)

Any help will be greatly appreciated.

 

[Goldenboy23]

Hi, Just one more thing, i am finding that the formula works perfectly if the indirect array contains numbers only e.g 71224 on the individual sheet, but it say for example the cells in the individual sheets contain a mixture of text and numbers e.g if A2 on the open sheet contains 71-71224-71225-55 and A3 contains 71-71224-71228-58 .... and so on (and this is the case for all the sheets), then the return on the front sheet is blank. Is there any solution to this ?

 

[estevaoba]

Hi. I'm afraid this is real hard to pull off with formulas, if at all possible.
All because you need that data in ascending order, which is easy to manage with numbers, but when it comes to text, you don't have a reference.
I can have the data bunched up in Front sheet, according to row position, but not in ascending order.

There may be some way to accomplish that with VBA, but I'm not familiar with that.
Maybe some other Forum user will be able to help you on that.

Good luck!

 

[Peter_SSs]

Hi thank you for this... i have sent you a private message☺

OK. I’ll take a look.

Please refer to #4 of the Forum Rules & in the future keep all thread-related correspondence in the public forum.

There are many helpers in the forum that may want to join the thread and have a different (& possibly better) approach. As soon as something is provided privately, those additional helpers do not have the full information and will probably just bypass the thread instead of helping. Additionally, the forum owner wishes the forum to be a place where anybody can search for information. Anything provided privately will have no chance of showing up in searches.

 

[Goldenboy23]

O.K thank you for this information. I think i may find a work around this by manipulating the information on the individual sheet to take out the "-" of the string i.e. so A1 would be 71, A2 71224, A3 71225 and so on. This would be the case on each of the separate sheets and it would pull through to the front sheet as follows

Open
A1 A2 A3
71 71224 71225

The effect of this would be that the separate sections on the front sheet would have 3 columns pulling through each from the separate sheets.

i have attempted to do this myself but have only got as far a manipulating the data on the sheets. I have been unable to pull through the respective columns on the separate sheet to the front sheet. Do you know how this can be achieved ?

Many Kind regards,

Goldenboy

 

[estevaoba]

HI!
I guess we could try and do the same with three instead of one helper column for each source tab.
Please post a sample table of Open tab, for example, with representative, but not sensitive data.

 

[Goldenboy23]

Hello,

So this is exactly what I am looking to achieve;

There are in total 5 sheets (4 sub-sheets and one front-sheet). Namely, the front-sheet, Prior,Posted Payment, Current1 and Current2.

Each row on each sheet relates to an account string combination. The front is a reconciliation of identical string combinations which exist across different sections.

Example;

Prior:
[TABLE="width: 259"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]comb1
[/TD]
[TD]comb2
[/TD]
[TD]Balance
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]000000
[/TD]
[TD]000000
[/TD]
[TD]900
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802201
[/TD]
[TD]802201
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802853
[/TD]
[TD]802851
[/TD]
[TD]300
[/TD]
[/TR]
</tbody>[/TABLE]



Current 1
[TABLE="width: 266"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]comb1
[/TD]
[TD]comb2
[/TD]
[TD]Balance
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]000000
[/TD]
[TD]000000
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802651
[/TD]
[TD]802651
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802851
[/TD]
[TD]802851
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802853
[/TD]
[TD]802853
[/TD]
[TD]800
[/TD]
[/TR]
</tbody>[/TABLE]


Current 2
[TABLE="width: 259"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]comb1
[/TD]
[TD]comb2
[/TD]
[TD]Balance
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]000000
[/TD]
[TD]000000
[/TD]
[TD]-300
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802651
[/TD]
[TD]802651
[/TD]
[TD]-100
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802851
[/TD]
[TD]802851
[/TD]
[TD]-200
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802853
[/TD]
[TD]802853
[/TD]
[TD]-800
[/TD]
[/TR]
</tbody>[/TABLE]


Posted:

[TABLE="width: 266"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]comb1
[/TD]
[TD]comb2
[/TD]
[TD]Balance
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]000000
[/TD]
[TD]000000
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802651
[/TD]
[TD]802651
[/TD]
[TD]400
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802851
[/TD]
[TD]802851
[/TD]
[TD]700
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802853
[/TD]
[TD]802851
[/TD]
[TD]500
[/TD]
[/TR]
</tbody>[/TABLE]


Payment:
[TABLE="width: 266"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]comb1
[/TD]
[TD]comb2
[/TD]
[TD]Balance
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]000000
[/TD]
[TD]000000
[/TD]
[TD]700
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802201
[/TD]
[TD]802201
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802651
[/TD]
[TD]802651
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]712070
[/TD]
[TD]802851
[/TD]
[TD]802851
[/TD]
[TD]500
[/TD]
[/TR]
</tbody>[/TABLE]

I am looking to build formulas on the front-sheet so each string from each sheet lines up (across section rows) with identical strings where these exist. If not there should be blanks.

Example:

Front-sheet;

[TABLE="width: 1200"]
<colgroup><col width="51"><col width="50"><col width="64"><col width="50"><col width="54"><col width="50"><col width="64"><col width="42"><col width="48"><col width="50"><col width="64"><col width="41"><col width="54"><col width="50"><col width="64"><col width="64"><col width="41"><col width="44"><col width="56"><col width="52"><col width="64"><col width="51"><col width="32"></colgroup><tbody>[TR]
[TD="class: et6, width: 216, colspan: 4, align: center"]Prior[/TD]
[TD="class: et6, width: 222, colspan: 4, align: center"]Posted[/TD]
[TD="class: et6, width: 210, colspan: 4, align: center"]Payment[/TD]
[TD="class: et22, width: 54, align: center"](D+H-L)[/TD]
[TD="class: et23, width: 228, colspan: 4, align: center"]Current1[/TD]
[TD="class: et22, width: 44, align: center"](M-Q) [/TD]
[TD="class: et29, width: 228, colspan: 4, align: center"]Current2[/TD]
[TD="class: et22, width: 32, align: center"](R-W)[/TD]
[/TR]
[TR]
[TD="class: et11, width: 51, align: center"]Account[/TD]
[TD="class: et12, width: 50, align: center"]comb1[/TD]
[TD="class: et12, width: 64, align: center"]comb2[/TD]
[TD="class: et13, width: 50, align: center"]Balance[/TD]
[TD="class: et11, width: 54, align: center"]Account[/TD]
[TD="class: et12, width: 50, align: center"]comb1[/TD]
[TD="class: et12, width: 64, align: center"]comb2[/TD]
[TD="class: et13, width: 42, align: center"]Balance[/TD]
[TD="class: et11, width: 48, align: center"]Account[/TD]
[TD="class: et12, width: 50, align: center"]comb1[/TD]
[TD="class: et12, width: 64, align: center"]comb2[/TD]
[TD="class: et13, width: 41, align: center"]Balance[/TD]
[TD="class: et11, width: 50, align: center"]Account[/TD]
[TD="class: et12, width: 64, align: center"]comb1[/TD]
[TD="class: et12, width: 64, align: center"]comb2[/TD]
[TD="class: et13, width: 41, align: center"]Balance[/TD]
[TD="class: et11, width: 56, align: center"]Account[/TD]
[TD="class: et12, width: 52, align: center"]comb1[/TD]
[TD="class: et12, width: 64, align: center"]comb2[/TD]
[TD="class: et33, width: 51, align: center"]Balance[/TD]
[/TR]
[TR]
[TD="class: et6, width: 216, colspan: 4, align: center"][/TD]
[TD="class: et6, width: 222, colspan: 4, align: center"][/TD]
[TD="class: et6, width: 210, colspan: 4, align: center"][/TD]
[TD="class: et26, width: 54, align: center"][/TD]
[TD="class: et6, width: 228, colspan: 4, align: center"][/TD]
[TD="class: et35, width: 44, align: center"][/TD]
[TD="class: et6, width: 228, colspan: 4, align: center"][/TD]
[/TR]
[TR]
[TD="class: et14, width: 51"]712070[/TD]
[TD="class: et14, width: 50"]000000[/TD]
[TD="class: et14, width: 64"]000000[/TD]
[TD="class: et14, width: 50"]900[/TD]
[TD="class: et15, width: 54"]712070[/TD]
[TD="class: et14, width: 50"]000000[/TD]
[TD="class: et14, width: 64"]000000[/TD]
[TD="class: et16, width: 42"]100.00[/TD]
[TD="class: et14, width: 48"]712070[/TD]
[TD="class: et14, width: 50"]000000[/TD]
[TD="class: et14, width: 64"]000000[/TD]
[TD="class: et14, width: 41"]700[/TD]
[TD="class: et27, width: 54"]300.00[/TD]
[TD="class: et14, width: 50"]712070[/TD]
[TD="class: et14, width: 64"]000000[/TD]
[TD="class: et14, width: 64"]000000[/TD]
[TD="class: et37, width: 41"]300[/TD]
[TD="class: et27, width: 44"]0.00[/TD]
[TD="class: et14, width: 56"]712070[/TD]
[TD="class: et14, width: 52"]000000[/TD]
[TD="class: et14, width: 64"]000000[/TD]
[TD="class: et18, width: 51"]-300.00[/TD]
[TD="class: et39, width: 32"]0.00[/TD]
[/TR]
[TR]
[TD="class: et14, width: 51"]712070[/TD]
[TD="class: et14, width: 50"]802201[/TD]
[TD="class: et14, width: 64"]802201[/TD]
[TD="class: et14, width: 50"]150[/TD]
[TD="class: et17, width: 54"][/TD]
[TD="class: et14, width: 50"][/TD]
[TD="class: et14, width: 64"][/TD]
[TD="class: et18, width: 42"][/TD]
[TD="class: et14, width: 48"]712070[/TD]
[TD="class: et14, width: 50"]802201[/TD]
[TD="class: et14, width: 64"]802201[/TD]
[TD="class: et14, width: 41"]150[/TD]
[TD="class: et28, width: 54"]0.00[/TD]
[TD="class: et14, width: 50"][/TD]
[TD="class: et14, width: 64"][/TD]
[TD="class: et14, width: 64"][/TD]
[TD="class: et18, width: 41"][/TD]
[TD="class: et28, width: 44"]0.00[/TD]
[TD="class: et14, width: 56"][/TD]
[TD="class: et14, width: 52"][/TD]
[TD="class: et14, width: 64"][/TD]
[TD="class: et18, width: 51"][/TD]
[TD="class: et40, width: 32"]0.00[/TD]
[/TR]
[TR]
[TD="class: et14, width: 51"][/TD]
[TD="class: et14, width: 50"][/TD]
[TD="class: et14, width: 64"][/TD]
[TD="class: et14, width: 50"][/TD]
[TD="class: et17, width: 54"]712070[/TD]
[TD="class: et14, width: 50"]802651[/TD]
[TD="class: et14, width: 64"]802651[/TD]
[TD="class: et18, width: 42"]400.00[/TD]
[TD="class: et14, width: 48"]712070[/TD]
[TD="class: et14, width: 50"]802651[/TD]
[TD="class: et14, width: 64"]802651[/TD]
[TD="class: et14, width: 41"]300[/TD]
[TD="class: et28, width: 54"]100.00[/TD]
[TD="class: et14, width: 50"]712070[/TD]
[TD="class: et14, width: 64"]802651[/TD]
[TD="class: et14, width: 64"]802651[/TD]
[TD="class: et18, width: 41"]100.00[/TD]
[TD="class: et28, width: 44"]0.00[/TD]
[TD="class: et14, width: 56"]712070[/TD]
[TD="class: et14, width: 52"]802651[/TD]
[TD="class: et14, width: 64"]802651[/TD]
[TD="class: et18, width: 51"]-100.00[/TD]
[TD="class: et40, width: 32"]0.00[/TD]
[/TR]
[TR]
[TD="class: et19, width: 51"][/TD]
[TD="class: et1, width: 50"][/TD]
[TD="class: et1, width: 64"][/TD]
[TD="class: et21, width: 50"][/TD]
[TD="class: et17, width: 54"]712070[/TD]
[TD="class: et14, width: 50"]802851[/TD]
[TD="class: et14, width: 64"]802851[/TD]
[TD="class: et18, width: 42"]700.00[/TD]
[TD="class: et14, width: 48"]712070[/TD]
[TD="class: et14, width: 50"]802851[/TD]
[TD="class: et14, width: 64"]802851[/TD]
[TD="class: et14, width: 41"]500[/TD]
[TD="class: et28, width: 54"]200.00[/TD]
[TD="class: et14, width: 50"]712070[/TD]
[TD="class: et14, width: 64"]802851[/TD]
[TD="class: et14, width: 64"]802851[/TD]
[TD="class: et18, width: 41"]200.00[/TD]
[TD="class: et28, width: 44"]0.00[/TD]
[TD="class: et14, width: 56"]712070[/TD]
[TD="class: et14, width: 52"]802851[/TD]
[TD="class: et14, width: 64"]802851[/TD]
[TD="class: et37, width: 51"]-200[/TD]
[TD="class: et40, width: 32"]0.00[/TD]
[/TR]
[TR]
[TD="class: et17, width: 51"]712070[/TD]
[TD="class: et14, width: 50"]802853[/TD]
[TD="class: et14, width: 64"]802851[/TD]
[TD="class: et21, width: 50"]300.00[/TD]
[TD="class: et17, width: 54"]712070[/TD]
[TD="class: et14, width: 50"]802853[/TD]
[TD="class: et14, width: 64"]802851[/TD]
[TD="class: et18, width: 42"]500.00[/TD]
[TD="class: et14, width: 48"][/TD]
[TD="class: et14, width: 50"][/TD]
[TD="class: et14, width: 64"][/TD]
[TD="class: et14, width: 41"][/TD]
[TD="class: et28, width: 54"]800.00[/TD]
[TD="class: et14, width: 50"]712070[/TD]
[TD="class: et14, width: 64"]802853[/TD]
[TD="class: et14, width: 64"]802853[/TD]
[TD="class: et18, width: 41"]800.00[/TD]
[TD="class: et28, width: 44"]0.00[/TD]
[TD="class: et14, width: 56"]712070[/TD]
[TD="class: et14, width: 52"]802853[/TD]
[TD="class: et14, width: 64"]802853[/TD]
[TD="class: et37, width: 51"]-800[/TD]
[TD="class: et40, width: 32"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

i hope i have explained this properly.

Kindest regards,

 

[estevaoba]

Sorry to report that my method does not work in this new arrangement.
Problem is, after your strings were manipulated, data is not consistent as for unique, rankable numbers.
712070, for instance, which I assume is a code, appears in all rows, so that is misleading to address correct rows.

It occurred to me that maybe you could try assigning new, unique sequence numbers for each account string, in a helper column, in a source tab where all accounts are found.
Use vlookup or index/match to place those new sequential numbers next to respective account strings in the other source tabs.
And from those new sequential numbers, you could use the model I posted earlier.

Then in new columns in Front Sheet, use vlookup or index/match formula the other way around to place the correct account string for each sequential number.

It's laborious, probably a long shot, but it may work.

Good luck!

 

[Goldenboy23]

Never mind ... thank you for all your help and assistance.